code(VBA) fail when reading most of the data , but work on some why?

[Darkforce]

I have this code. it seens to work on the 3 first(row 2 ,3,4) line of data but seens to fail on all other of the line then that like this row 5

any idear why ?

Dim rownr
rownr = 4
Do Until Cells(rownr, 1).Value = ""
sal = ""
Dim E_name As String
E_name = ""
E_name = CStr(Ark1.Cells(rownr, 1).Value)
MsgBox (E_name)
'Why does this line fail ?
sal = Application.WorksheetFunction.VLookup(E_name, Ark1.Range("A:D"), 3, False)
'line end
MsgBox "Salary is : $ " & sal
sal = ""
rownr = rownr + 200
Loop

End Sub

 

[StephenCrump]

It's really not clear what you are doing here ...

You say your code works on rows 2,3 and 4, but your row counter starts at 4, and increments by 200.

And it looks like E_name and the VLOOKUP are both pointing at the same place in Ark1?

You haven't made clear what you mean by "fail" but I'm guessing you're getting a Run-time error 1004: Unable to get the VLookup property of the Worksheet function class? Which simply means your VLOOKUP is not finding a match.

That could be because you have numbers in column A, but E_name is converting your lookup_value to a string?

 

[Darkforce]

It's really not clear what you are doing here ...

You say your code works on rows 2,3 and 4, but your row counter starts at 4, and increments by 200.

And it looks like E_name and the VLOOKUP are both pointing at the same place in Ark1?

You haven't made clear what you mean by "fail" but I'm guessing you're getting a Run-time error 1004: Unable to get the VLookup property of the Worksheet function class? Which simply means your VLOOKUP is not finding a match.

That could be because you have numbers in column A, but E_name is converting your lookup_value to a string?

well the +200 is only so I do not have to Click ok 500 times each time I try to run the code. .. I don´t have the hotkey needed on my laptop stop the code when it is started


I can chance the start row in the code just to test where the problem is .


what I doing is trying to step by step learding the thing i need to a most bigger project.. I want to make a excel sheet that get data form an sql .. and combi it with some text that the user better understand .


end project is somthing like this

where the data get updatet.. so the uses easy can see what each materiale cost

 

[StephenCrump]

If I understand this correctly, I think you are saying that when rownr = 5, the code "fails" on this line:

sal = Application.WorksheetFunction.VLookup(E_name, Ark1.Range("A:D"), 3, False)

What do you mean by "fails"? Is sal not what you were expecting? Or are you getting a run-time error, because the VLookup is not finding a match?

Is the value in cell A5 of worksheet Ark1 3111012012, as shown in your picture? Is this a number? Or is it a text value?

 

[Darkforce]

ean by "fails"? Is sal not what you we

If I understand this correctly, I think you are saying that when rownr = 5, the code "fails" on this line:

sal = Application.WorksheetFunction.VLookup(E_name, Ark1.Range("A:D"), 3, False)

What do you mean by "fails"? Is sal not what you were expecting? Or are you getting a run-time error, because the VLookup is not finding a match?

Is the value in cell A5 of worksheet Ark1 3111012012, as shown in your picture? Is this a number? Or is it a text value?

I get an error ... think it is an 1004 if i remember correcly