Updating Excel Database---really need some help

[Mkammari]

I'm going to try to ask this one more time, because I can't figure this out myself and it's the last piece of my project.

I have developed multiple VBA userforms requiring data entry from a user. Once all of the input is completed, the user will hit a command button to generate a quote depending on the inputs. I have written code that books all of the data on the quote form to an excel sheet title "Quotes". There are fields for quote number, customer name, csr name, date....this information is input horizontally..ie Column A..is quote number, column b is customer name, column c is csr name..etc....

I have also created a form for retrieval of these records using a vlook up function with no problem. The problem comes when the user needs to update the quote because some of the information has changed. For instance, the address has changed or the customer doesn't want a certain item.

What I would like to do, is write a piece of code that takes the retrieved record, finds the exact line on where it is located on the database and change update the information to reflect the new pricing and new information. I do not want to create an extra line.

I'm not really a whiz at vba but I know it has functionality to do this. This is the third time I've asked. I hope this information is good enough and I would really appreciate your help and expertise with this code.

Thank You

 

[acw]

Hi

Broadly, you should be able to identify the row on the quote sheet that houses the current information. Once you have that row, the basically you output the information that is currently on the form to the relevant place on the quote sheet, but using the existing row number. Not really much different from adding a new entry, only instead of using the next available row, you use the existing row.

HTH

Tony

 

[Mkammari]

Thank you for the reply...and yes that is exactly what I am trying to do.

Not sure about the code thought...for example

Once the quote number is found on the database, we'll call it "Quotes Archive",
then would the code be something similar to an offset (1,0) type code.

It would need to search the database for the quote number first, then paste over all of the existing information with the new calculations and field changes.

 

[acw]

Hi

More like

'to get the existing row for the quote number
outputrow = worksheetfunction.match(quoteno, range("A:A"),0)
assuming that the quote number is in column A

the output is then
cells(outputrow,"B").value = textbox1.value
naturally modified to suit your situation.

If you are not on the output sheet, the you will have to either quote the sheet name in the references, or set a variable to the sheet name.

Another alternative using the offset function would be
set outplace = sheets("quotes archive").range("a" & outputrow).

Then
outplace.offset(0,1).value = .....


HTH

Tony

 

[Mkammari]

Tony, I think that may work. I will try it out tomorrow morning. This has given me some kind of headache, and I just wanted to thank you so very much for your attention to the matter. I have a great respect for you guys and your knowledge. Thanks and I think I can handle it from here.

Michael

 

[Mkammari]

Tony, I thought that solution would work...but I'm getting a run time error of 1004.."unable to get match property of worksheet class"


here is the code.

Sheets("quotedata").Activate
Dim Findrow As Long
Findrow = WorksheetFunction.Match(txtQtenumqr.Text, ("A:A"), 0)
txtQtenumqr.Text = Cells(Findrow, 1)
TxtCrnDateqr.Text = Cells(Findrow, 2)
TxtCsrNameqr.Text = Cells(Findrow, 3)
TxtBoxBroLocqr.Text = Cells(Findrow, 4)
TxtCustNameQr.Text = Cells(Findrow, 5)
TxtPickAddrQR.Text = Cells(Findrow, 6)
txtpickaptQr.Text = Cells(Findrow, 7)
TxtPickUpLocQr.Text = Cells(Findrow, 8)
TxtzipQr.Text = Cells(Findrow, 9)
TxtPhoneQr.Text = Cells(Findrow, 10)
TxtFaxNumQr.Text = Cells(Findrow, 11)
TxtPickDateQr.Text = Cells(Findrow, 12)

 

[acw]

Hi

Is the data you are searching text or numeric? Also, why are you filling the txtQtenumqr when it would have already been filled to get the value to search???

Tony

 

[Mkammari]

The text is numeric....sorry about the filling of the quote number...Mistake on my part...the data will already be filled. Is the code I provided correct?

 

[Mkammari]

Tony...changed my code to this and I get a runtime error 91.."Object variable or with block variable not set"

Dim Findrow As Range
Dim quotenumber As Integer
quotenumber = txtQtenumqr.Text
Findrow = WorksheetFunction.Match(quotenumber, Sheets("quotedata").Range("A:A"), 0)
Cells(Findrow, 1) = txtQtenum.Text
Cells(Findrow, 2) = TxtCrnDate.Text
Cells(Findrow, 3) = TxtCsrNameq.Text
Cells(Findrow, 4) = TxtBoxBroLocq.Text
Cells(Findrow, 5) = TxtCustNameQuote.Text
Cells(Findrow, 6) = TxtPickAddrQuote.Text
Cells(Findrow, 7) = txtpickaptquote.Text
Cells(Findrow, 8) = TxtPickUpLocQuote.Text
Cells(Findrow, 9) = TxtzipQuote.Text
Cells(Findrow, 10) = TxtPhoneQuote.Text
Cells(Findrow, 11) = TxtFaxNumQuote.Text
Cells(Findrow, 12) = TxtPickDateQuote.Text
Cells(Findrow, 13) = TxtReferralQuote.Text
Cells(Findrow, 14) = TxtCust2Quote.Text
Cells(Findrow, 15) = TxtDelivAddrQuote.Text
Cells(Findrow, 16) = txtdelaptquote.Text
Cells(Findrow, 17) = TxtDelivLocQuote.Text
Cells(Findrow, 18) = TxtZip2Quote.Text
Cells(Findrow, 19) = txtaltnumquote.Text
Cells(Findrow, 20) = txtDelDateQuote.Text
Cells(Findrow, 21) = TxtDelTypequote.Text

 

[Mkammari]

Tony...I figured it out..Here it is

Dim Findrow As Long
Dim quotenumber As Integer
quotenumber = txtQtenumqr.Text
Findrow = WorksheetFunction.Match(quotenumber, Sheets("quotedata").Range("A:A"), 0)
Cells(Findrow, 2) = TxtCrnDateqr.Text
Cells(Findrow, 3) = TxtCsrNameqr.Text
Cells(Findrow, 4) = TxtBoxBroLocqr.Text
Cells(Findrow, 5) = TxtCustNameQr.Text
Cells(Findrow, 6) = TxtPickAddrQR.Text
Cells(Findrow, 7) = txtpickaptQr.Text
Cells(Findrow, 8) = TxtPickUpLocQr.Text
Cells(Findrow, 9) = TxtzipQr.Text
Cells(Findrow, 10) = TxtPhoneQr.Text
Cells(Findrow, 11) = TxtFaxNumQr.Text
Cells(Findrow, 12) = TxtPickDateQr.Text
Cells(Findrow, 13) = TxtReferralQr.Text
Cells(Findrow, 14) = TxtCust2Qr.Text
Cells(Findrow, 15) = TxtDelivAddrQr.Text
Cells(Findrow, 16) = txtdelaptQr.Text
Cells(Findrow, 17) = TxtDelivLocQr.Text
Cells(Findrow, 18) = TxtZip2Qr.Text
Cells(Findrow, 19) = txtaltnumQr.Text
Cells(Findrow, 20) = txtDelDateQr.Text
Cells(Findrow, 21) = TxtDelTypeQr.Text


Thanks for the help..godsend.