VBA Formula problem

[cdragone]

Hey
Theres a problem which i do not know how to fix. I've got most of the program i made working but i just cant get the VLOOKUP to work.
The program shall be used on multiple OS platforms (Win Vista, Win 7, Mac OS X 10). The excel version is 2007.
Anyway heres the code:

Private Sub InsertData_Click()
Dim lRow As Long
Dim fRow As Long
Dim ws As Worksheet
Set ws = Sheet1
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
fRow = Range("A65536").End(xlUp).Row + 1
ws.Cells(lRow, "A") = Me.BillNrBox.Value
ws.Cells(lRow, "B") = Me.SenderBox.Value
ws.Cells(lRow, "C") = Me.ReceiverBox.Value
ws.Cells(lRow, "D") = Me.KgBox.Value * 1
ws.Cells(lRow, "E") = Me.M3Box.Value * 333
ws.Cells(lRow, "F") = Me.LVBox.Value * 1850
ws.Cells(fRow, "G").Formula = "=MAX(D" & fRow & ":F" & fRow & ")"
[COLOR="Red"]ws.Cells(fRow, "H").Formula = "=VLOOKUP(G" & fRow & ";Prices!$A$2:$B$56;TRUE)"[/COLOR]
BillNrBox.Value = vbNullString
SenderBox.Value = vbNullString
ReceiverBox.Value = vbNullString
KgBox.Value = "0"
M3Box.Value = "0"
LVBox.Value = "0"
HelpBox.Caption = "Info added to Worksheet"
End Sub

At first the formula on G column did not also work, but got it working via searching. All help is appreciated.

PS: Runtime error 1004
Application-defined error or object-defined error

Thank you in Advance
-C

 

[VoG]

Try replacing ; with ,

 

[cdragone]

Thank you for the fast reply. I was traveling from 1 country to another so thats why it took so long time to check it.
Anyway no more errors with the "with," but the formula will appears as #NAME?

=VLOOKUP(G9 With;Prices!$A$2:$B$56 With;TRUE)

That's how it's in the cell.
Any suggestions how to get it working?

 

[VoG]

I don't know where "with" is coming from but (in the code and the formula) you are missing the third parameter. The formula in the cell should look like

=VLOOKUP(G9;Prices!$A$2:$B$56;2;TRUE)

 

[cdragone]

Thats true. However strange as it seemes to be:

ws.Cells(fRow, "H").Formula = "=VLOOKUP(G" & fRow & " With,Prices!$A$2:$B$56 With,TRUE)"

Dosen't get an error

ws.Cells(fRow, "H").Formula = "=VLOOKUP(G" & fRow & " With,Prices!$A$2:$B$56 With,2 With,TRUE)"

Gets an error

Tho the formula indeed was wrong. i'am kinda confused how come the code crashes.

 

[Hermanito]

Try to replace the

& fRow &

with

& Trim(Str(fRow)) &

I've seen it happen that Excel puts a space in front of a number if you rely on the implicit conversion from number to string. That would mean you end up with a formula saying VLOOKUP(G 9 etcetera, which would not work.

 

[cdragone]

nope, didnt work

 

[Hermanito]

Can you repost the full code as it is now, after all changes you made? And also indicate where the error occurs...

 

[cdragone]

Private Sub InsertData_Click()
Dim lRow As Long
Dim fRow As Long
Dim gRow As String
Dim ws As Worksheet
Set ws = Sheet1
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
fRow = Range("A65536").End(xlUp).Row + 1
ws.Cells(lRow, "A") = Me.BillNrBox.Value
ws.Cells(lRow, "B") = Me.SenderBox.Value
ws.Cells(lRow, "C") = Me.ReceiverBox.Value
ws.Cells(lRow, "D") = Me.KgBox.Value * 1
ws.Cells(lRow, "E") = Me.M3Box.Value * 333
ws.Cells(lRow, "F") = Me.LVBox.Value * 1850
ws.Cells(fRow, "G").Formula = "=MAX(D" & fRow & ":F" & fRow & ")"
ws.Cells(fRow, "H").Formula = "=VLOOKUP(G" & Trim(Str(fRow)) & ";Hinnad;2;TRUE)"
BillNrBox.Value = vbNullString
SenderBox.Value = vbNullString
ReceiverBox.Value = vbNullString
KgBox.Value = "0"
M3Box.Value = "0"
LVBox.Value = "0"
HelpBox.Caption = "Info added to Worksheet"
End Sub

Runtime error 1004
Application-defined or Object-defined error

 

[Hermanito]

ws.Cells(fRow, "G").Formula = "=MAX(D" & [COLOR=red]Trim(Str(fRow))[/COLOR] & ":F" & [COLOR=red]Trim(Str(fRow))[/COLOR] & ")"
ws.Cells(fRow, "H").Formula = "=VLOOKUP(G" & Trim(Str(fRow)) & "[B][COLOR=red],[/COLOR][COLOR=darkorange]Hinnad[/COLOR][COLOR=red],[/COLOR][/B]2[COLOR=red][B],[/B][/COLOR]TRUE)"

make the replacements in red, the same as you did in the second line.
Also replace the ; with , in the second line

Finally: what is Hinnad?? If that stays in, it will not work...