How do I write a Photography Price Quote?

Excel vba

• Hello I am writing a Quote system which takes data in rows A,B,F,G,H,I,J,K from a Price list from entry of a quantity in cell F in any of rows 4:51. Once entry is complete, this data is copied to 1st empty row in range 16:34 cols V,X,Y,Z,AA,AB,AC Then a macro is run which prints the Quotation saves it by name of customer in W7, then copies Quote Date, Number, Customer Name and Total value ( Cells AC2,AC4,W7,AC37 to Sheet"Forecast" and pastes the data to 1st empty row in A,B,C,D - Range A3:D40, then clears contents for next quote entry. Here is code that copies data from Quotation Sheet to Forecast Sheet Dim z As Long With ThisWorkbook.Sheets("Forecast").[A3:D40] For z = .Rows.Count To 1 Step -1 If .Cells(z, 1) & .Cells(z, 2) & .Cells(z, 3) & .Cells(z, 4) <> "" Then Exit For Next If z = .Rows.Count Then MsgBox "Prospect Lines full" Else .Cells(z + 1, 1) = Sheets("Quotation").[AC2].Value .Cells(z + 1, 2) = Sheets("Quotation").[AC4].Value .Cells(z + 1, 3) = Sheets("Quotation").[W7].Value .Cells(z + 1, 4) = Sheets("Quotation").[AC37].Value End If ActiveSheet.[AC2].Select End With This works OK except that the data is copied to Row 33 down not 3 down and I cannot see why?? any ideas most welcome. I haven'e done the macro to transfer price list data yet butI guess it will be similar. QUESTION: I am a VBA novice and just bought the John Walkenbach book on2007. A quick background on what I am trying to do: I have a large table with categories in one column (categories may repeat) and numerical values across a date range. I want to be able to sum the categories based on user input (for example, user may say: category 1 for dates x to y). I thought I would first find all rows in which category 1 occurs in the table, then identify the columns in which dates x - y occur. Then I would try to use some version of index/match in VBA to get to the sum. I have some basic questions: 1. I cannot figure out how to do the column lookup for the dates - I constantly get a endif without blockif error. Here is my code (dates are integer values) date1 = Sheet5.Range("d22") date2 = Sheet5.Range("e22") difference = date2 - date1 Start = date1 + 3 'three columns before dates start in the columns finish = Start + difference For column = Start To finish If Sheet5.Cells(2, column) = date1 Then row = row + 1 Sheet5.Cells(row, 6) = column Else If Sheet5.Cells(2, column) <> date1 Then For i = Start To finish date1 = date1 + 1 'Else: MsgBox ("failed") End If End If I have figured out how to do the rows using this code: Private Sub rowlookup_Click() Dim x Dim y Dim z y = Sheet5.Range("b21") z = 23 Sheet5.Range("b23:b30").Clear For x = 1 To 15 If Sheet5.Cells(x, 3) = y Then z = z + 1 Sheet5.Cells(z, 2) = x End If Next End Sub I don't know how to do the index/match. I want to be able to pick up the rows and columns generated by the above two functions and then do an index/match and generate the sums. Sorry for the super long question, but I hope you understand what I am trying to do. The endif without blockif has taken way too long for me to figure out, with no real result. For the index/match, I am trying something like this, but I know it won't work: Dim Testtable As Range 'Dim row As Integer 'Dim column As Integer 'Dim test As Integer ' ' 'Testtable = Sheet5.Range("a1:l25") 'row = Sheet5.Range("d22").Value 'column = Sheet5.Range("e22").Value ' 'test = WorksheetFunction.index(array_Testtable, row, column) ' 'Sheet5.Range("i26") = test ANSWER: Quasim, As I read your code, the user will enter the start date and end date in cells D22 and E22 respectively. Dates in your worksheet are found in row 2 Categories are listed in column 3 (column C) The user puts the desired category in B21 Data must be in rows 3 to 20 You do have code like testTable = sheet5.Range("A1:L25") but that would interfer with entries in D22 and E22 so I will write my answer with respect to the assumptions above. Data in D3:L20 Dates in D2:L3 categories in C3:C20 start date: D22 End date: E22 Category to sum: B21 Based on those assumption, this worked for me. Sub AddforSpecifiedCategoryandDates() Dim lastcolumn As Long, i As Long, r As Range Dim dSum As Double 'find the coumns with the start and end date lastcolumn = Sheet5.Cells(2, Columns.Count).End(xlToLeft).Column For i = 4 To lastcolumn Set r = Sheet5.Cells(2, i) If IsDate(r) Then If r.Value = Sheet5.Range("D22") Then startcol = i If r.Value = Sheet5.Range("E22") Then endcol = i End If Next ' find the rows with the category and as each is found, accumulate the ' the sum for that row For i = 3 To Sheet5.Cells(Rows.Count, "C").End(xlUp).Row If Sheet5.Cells(i, "C").Value = Sheet5.Range("B21") Then Set r1 = Sheet5.Cells(i, startcol).Resize(1, endcol - startcol + 1) dSum = dSum + Application.Sum(r1) End If Next MsgBox "sum is " & dSum End Sub ---------- FOLLOW-UP ---------- QUESTION: I apologize in my previous follow-up. At the end, the user would select date1-date2. I am looking to write a macro which should match each from sheet1!B:B to Sheet2!B:B. And value which doesn't found in sheet2!B:B then copy the whole row from sheet1 and paste in sheet3. Like if sheet1!B2 value not found in Sheet2!B2:B then copy the row Sheet1!2:2 and paste in row Sheet3!3:3. Regard Mohammad Ali This is Antonio, I'm doing a project in Excel, and I'm not good at all. My question is, how to write the code in VBA to identify the begin date and the end date for each employee absences in the time sheet. Also, the same information automatically shows on the other worksheet which is the Absences Batch Input. If You have any question please do not hesitate to contact me. I would like to send you the specificworksheet to see what I mean. Antonio Tando My question may seem very simple to u. I am new to VBA. Hope i'll get help from you. Question: I have a table containing Number, Name, Description, count, product and so on. Here i need to retrieve the whole details for particular Number. Number can be entered in any of the cell and button is clicked to get the data. Pls do help me out. Dear You have helped me before, so I hope you can help this time. I am newish to VBA. I have a workbook with 2 worksheets. I am trying to write a formula in worksheet 1, column A that ranks all scores from worksheet 2, column A. However, the size of worksheet 2 changes and I cannot understand how I can use the "finalrow" variable in a R1C1 formula, when there are 2 worksheets. I have set the variable finalrow for worksheet2 and that works, but I cannot get this to work in the formula. I have tried the following:= Selection.FormulaR1C1 = _ "=RANK('Points Formula Sheet'!R[]C1,'Points Formula Sheet'!R[]c1:r[FinalRow]c1" but it won't work. Can you help please?? Lesley QUESTION: Hi Isaac - I am a retired IT consultant living in a small rural community in Australia and seem to have become the local "Help desk"! I have created a blank invoice for a local plumber whose computer skills are very poor. He wants to be able to increase the invoice number by one each time he opens the blank spreadsheet. I can do that using the following macro code: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wks As Worksheet Dim rngCounter As Range Set wks = ThisWorkbook.Sheets("Sheet1") Set rngCounter = wks.Range("i5") rngCounter.NumberFormat = "@" If rngCounter.Formula = "" Then rngCounter = "0001" ElseIf IsError(rngCounter + 0) Then rngCounter = "0001" Else rngCounter = rngCounter + 1 rngCounter = Application.WorksheetFunction.Rept( _ "0", 4 - Len(rngCounter)) & rngCounter End If Me.Save End Sub --- But that increases the number by one when the spreadsheet is closed but he closes it by renaming it with customer data in the save command. Then when e clicks on the blank sheet again, the number is the same. He has to click on the previous saved sheet to get the increased number change but that means he has to blank out or overwrite the previous data and that is potentially dangerous for him. Is it possible to increase the invoice number in a cell by one each time one opens a blank spreadsheet, permit the user to enter data and then save it under a different name, and then open the blank sheet again with the invoice number increased by one? I am using2007 but I am saving the output as97 -2003 because the plumber has Officeon his computer. Thanks in advance - John ANSWER: * take all the code you have, that's working, and instead of putting it in the Before close event, put it in the Before save event. It will fire for Save or Save As. ---------- FOLLOW-UP ---------- QUESTION: Thanks for your prompt reply Isaac - I did as you suggested but got the following error: Microsoft Visual Basic Compile error: Procedure declaration does not match description of event or procedure having the same name I'm unable to resolve this. Regards John ANSWER: Can you either paste ALL the code you're using, in a post of question, or else email it to [email protected] ---------- FOLLOW-UP ---------- QUESTION: This is the code I am using: Private Sub Workbook_BeforeSave(Cancel As Boolean) Dim wks As Worksheet Dim rngCounter As Range Set wks = ThisWorkbook.Sheets("Sheet1") Set rngCounter = wks.Range("i5") rngCounter.NumberFormat = "@" If rngCounter.Formula = "" Then rngCounter = "0001" ElseIf IsError(rngCounter + 0) Then rngCounter = "0001" Else rngCounter = rngCounter + 1 rngCounter = Application.WorksheetFunction.Rept( _ "0", 4 - Len(rngCounter)) & rngCounter End If Me.Save End Sub

• Answer:

  Tony, I tested your code and cells AC2, AC4, W7 and AC37 are copied perfectly into Sheets("Forecast"), just under the last row. I don't understand why at your end it's copied down to row 33. Can you be more specific please. Your code works perfectly at my end. Rgds, Tony, Is it possible for you to send me a copy of your spreadsheet to: [email protected]? Qasim If that is the case - that the user will select entries from row 1 rather than row 2, then the code would need to be modified like this (which produced the 34 when D22 held date1 and E22 held date2 (literally). Sub AddforSpecifiedCategoryandDates_Modified() Dim lastcolumn As Long, i As Long, r As Range Dim dSum As Double 'find the coumns with the start and end date lastcolumn = Sheet5.Cells(2, Columns.Count).End(xlToLeft).Column For i = 4 To lastcolumn Set r = Sheet5.Cells(1, i) ' If IsDate(r) Then If LCase(r.Value) = LCase(Sheet5.Range("D22")) Then startcol = i If LCase(r.Value) = LCase(Sheet5.Range("E22")) Then endcol = i ' End If Next ' find the rows with the category and as each is found, accumulate the ' the sum for that row For i = 3 To Sheet5.Cells(3, "C").End(xlDown).Row If Sheet5.Cells(i, "C").Value = Sheet5.Range("B21") Then Set r1 = Sheet5.Cells(i, startcol).Resize(1, endcol - startcol + 1) dSum = dSum + Application.Sum(r1) End If Next MsgBox "sum is " & dSum End Sub Mohammad Ali, Sub copyData() Dim r1 As Range, r2 As Range Dim cell1 As Range With Worksheets("Sheet1") Set r1 = .Range("B2", .Cells(.Rows.Count, 2).End(xlUp)) End With With Worksheets("Sheet2") Set r2 = .Range("B2", .Cells(.Rows.Count, 2).End(xlUp)) End With For Each cell1 In r1 If Application.CountIf(r2, cell1) = 0 Then With Worksheets("Sheet3") cell1.EntireRow.Copy .Cells(.Rows.Count, "B").End(xlUp).Offset(1, -1) End With End If Next End Sub worked for me. Antonio, I guess I would need to see the sheet to assess what you want because what you describe is way to vague to use as a basis for constructing code. send it to (email address should appear in the next line) Ashwini I don't think you need a macro to accomplish what you want, I believe you can find your information with a VLOOKUP formula. For example: Assume your data is in an array that includes A1 thru C100, with A being the column that contains the number you want to look up and B anC columns being the data pertinant to each number. The formula you could use would look like this =VLOOKUP($E$1,$A$1:$C$100,1,FALSE) This formula says go to the array A1 to C100 and look up the value that is in cell E1, when you find it return the value from column 1 of the array that is on the same row. The false in the formula says only look up the exact number. The next formula is exactly the same but it says return the data from the second column of the array. =VLOOKUP($E$1,$A$1:$C$100,2,FALSE) The third formula is exactly the same except it says return the value from column 3 of the array. =VLOOKUP($E$1,$A$1:$C$100,3,FALSE) With those formulas in place you can put a value in cell E1 andwill look it up in the array and bring back the pertinent data from the array columns that your formulas reference. Of course you can change the formulas to fit your particular circumstances but something similar to these should work for you. Lesley, when you want to use a variable value in building a string, you have to concatenate the variable value. You can't just type it inside the double quotation marks. Selection.FormulaR1C1 = _ "=RANK('Points Formula Sheet'!R[]C1,'Points Formula Sheet'!R[]c1:r[" & FinalRow & "]c1" So sorry for delay in answer. all of my questions are sitting unanswered my wife's father passed and everything has been hectic. I think you need to chagne to this Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wks As Worksheet Dim rngCounter As Range Set wks = ThisWorkbook.Sheets("Sheet1") Set rngCounter = wks.Range("i5") rngCounter.NumberFormat = "@" If rngCounter.Formula = "" Then rngCounter = "0001" ElseIf IsError(rngCounter + 0) Then rngCounter = "0001" Else rngCounter = rngCounter + 1 rngCounter = Application.WorksheetFunction.Rept( _ "0", 4 - Len(rngCounter)) & rngCounter End If Me.Save End Sub