vba problem with lookup

T

thomas donino

Everything is working fine in this code until after strTemp1 =Left(strTemp1,1),
which = "5"

The Worksheets("Tables").Range("TranslationTable") is a valid table. I
checked by going to Formula>Name manager and checking to see if it was
correct.
But I get a runtime error 1004:
Unable to get the VLookup property of the WorksheetFunction class

I also made sure the cells in the TransactionTable range were set as text so
that a 5 in the cell should be the same as a 5 returned by strTemp1 but that
didn't help either

In the code below I hardcoded the table range to see if that worked but it
did not.

Any suggestions?
Sub TranslateSyntax()

Dim strOne As String, strTwo As String
Dim strFound1 As String, strFound2 As String, strFound3 As String, strFound4
As String
Dim strPart1 As String, strPart2 As String, strPart3 As String, strPart4 As
String
Dim iStrtCol As Integer, iStrtRow As Integer, iEndRow As Integer
Dim EndCol As Integer, NewEndCol As Integer
Dim strTemp1 As String
Dim tabRange As Range

iStrtRow = Cells.Find(What:="when", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="when", MatchCase:=True).Column
iEndRow = Cells(iStrtRow, iStrtCol).End(xlDown).Row

strFound1 = LTrim(Cells(iStrtRow + 1, iStrtCol).Value)
strFound2 = LTrim(Cells(iStrtRow + 3, iStrtCol).Value)
strFound3 = LTrim(Cells(iStrtRow + 5, iStrtCol).Value)

' find the last show day ie 5d = 5 days later
iStrtRow = Cells.Find(What:="show", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="show", MatchCase:=True).Column
For i = iStrtRow To Cells.Find(What:="when", MatchCase:=True).Row - 1

If Cells(, iStrtCol).Value = "" Then
Exit For
Else
strTemp1 = Trim(Cells(, iStrtCol).Value)
strTemp1 = Left(strTemp1, 2)
End If
Next i
strTemp1 = Left(strTemp1, 1)
Set tabRange = Worksheets("Tables").Range("e1:f20")
strTemp1 = WorksheetFunction.VLookup(strTemp1, tabRange, 2, False)


End Sub
 
J

Joel

Strtemp1 could be empty when you exit the for loop which may cause an error.
I added a test for this condition and made some change in the code to match
my style of programming. the code below will run faster because you are not
finding the same location over and over again.


Sub TranslateSyntax()

Dim strOne As String, strTwo As String
Dim strFound1 As String
Dim strFound2 As String
Dim strFound3 As String
Dim strFound4 As String
Dim strPart1 As String
Dim strPart2 As String
Dim strPart3 As String
Dim strPart4 As String
Dim iStrtCol As Integer
Dim iStrtRow As Integer
Dim iEndRow As Integer
Dim EndCol As Integer
Dim NewEndCol As Integer
Dim strTemp1 As String
Dim tabRange As Range
Dim WhenCell As Range
Dim ShowCell As Range

Set WhenCell = Cells.Find(What:="when", MatchCase:=True)

iEndRow = WhenCell.End(xlDown).Row

strFound1 = LTrim(Cells(WhenCell.Offset(1, 0).Value))
strFound2 = LTrim(WhenCell.Offset(3, 0).Value)
strFound3 = LTrim(WhenCell.Offset(5, 0).Value)

' find the last show day ie 5d = 5 days later
Set ShowCell = Cells.Find(What:="Show", MatchCase:=True)

For i = ShowCell.Row To (WhenCell.Row - 1)
RowOffset = i - ShowCell.Row

If ShowCell.Offset(RowOffset, 0).Value = "" Then
Exit For
Else
strTemp1 = Trim(ShowCell.Offset(RowOffset, 0).Value)
strTemp1 = Left(strTemp1, 2)
End If
Next i

if StrTemp1 <> "" then

strTemp1 = Left(strTemp1, 1)
Set tabRange = Worksheets("Tables").Range("e1:f20")
strTemp1 = WorksheetFunction.VLookup(strTemp1, tabRange, 2, False)
else
msgbox("Error : strTemp1 is empty")
end if

End Sub


thomas donino said:
Everything is working fine in this code until after strTemp1 =Left(strTemp1,1),
which = "5"

The Worksheets("Tables").Range("TranslationTable") is a valid table. I
checked by going to Formula>Name manager and checking to see if it was
correct.
But I get a runtime error 1004:
Unable to get the VLookup property of the WorksheetFunction class

I also made sure the cells in the TransactionTable range were set as text so
that a 5 in the cell should be the same as a 5 returned by strTemp1 but that
didn't help either

In the code below I hardcoded the table range to see if that worked but it
did not.

Any suggestions?
Sub TranslateSyntax()

Dim strOne As String, strTwo As String
Dim strFound1 As String, strFound2 As String, strFound3 As String, strFound4
As String
Dim strPart1 As String, strPart2 As String, strPart3 As String, strPart4 As
String
Dim iStrtCol As Integer, iStrtRow As Integer, iEndRow As Integer
Dim EndCol As Integer, NewEndCol As Integer
Dim strTemp1 As String
Dim tabRange As Range

iStrtRow = Cells.Find(What:="when", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="when", MatchCase:=True).Column
iEndRow = Cells(iStrtRow, iStrtCol).End(xlDown).Row

strFound1 = LTrim(Cells(iStrtRow + 1, iStrtCol).Value)
strFound2 = LTrim(Cells(iStrtRow + 3, iStrtCol).Value)
strFound3 = LTrim(Cells(iStrtRow + 5, iStrtCol).Value)

' find the last show day ie 5d = 5 days later
iStrtRow = Cells.Find(What:="show", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="show", MatchCase:=True).Column
For i = iStrtRow To Cells.Find(What:="when", MatchCase:=True).Row - 1

If Cells(, iStrtCol).Value = "" Then
Exit For
Else
strTemp1 = Trim(Cells(, iStrtCol).Value)
strTemp1 = Left(strTemp1, 2)
End If
Next i
strTemp1 = Left(strTemp1, 1)
Set tabRange = Worksheets("Tables").Range("e1:f20")
strTemp1 = WorksheetFunction.VLookup(strTemp1, tabRange, 2, False)


End Sub
Click to expand...
 
T

thomas donino

I am running out but i will test later, thank you

Joel said:
Strtemp1 could be empty when you exit the for loop which may cause an error.
I added a test for this condition and made some change in the code to match
my style of programming. the code below will run faster because you are not
finding the same location over and over again.


Sub TranslateSyntax()

Dim strOne As String, strTwo As String
Dim strFound1 As String
Dim strFound2 As String
Dim strFound3 As String
Dim strFound4 As String
Dim strPart1 As String
Dim strPart2 As String
Dim strPart3 As String
Dim strPart4 As String
Dim iStrtCol As Integer
Dim iStrtRow As Integer
Dim iEndRow As Integer
Dim EndCol As Integer
Dim NewEndCol As Integer
Dim strTemp1 As String
Dim tabRange As Range
Dim WhenCell As Range
Dim ShowCell As Range

Set WhenCell = Cells.Find(What:="when", MatchCase:=True)

iEndRow = WhenCell.End(xlDown).Row

strFound1 = LTrim(Cells(WhenCell.Offset(1, 0).Value))
strFound2 = LTrim(WhenCell.Offset(3, 0).Value)
strFound3 = LTrim(WhenCell.Offset(5, 0).Value)

' find the last show day ie 5d = 5 days later
Set ShowCell = Cells.Find(What:="Show", MatchCase:=True)

For i = ShowCell.Row To (WhenCell.Row - 1)
RowOffset = i - ShowCell.Row

If ShowCell.Offset(RowOffset, 0).Value = "" Then
Exit For
Else
strTemp1 = Trim(ShowCell.Offset(RowOffset, 0).Value)
strTemp1 = Left(strTemp1, 2)
End If
Next i

if StrTemp1 <> "" then

strTemp1 = Left(strTemp1, 1)
Set tabRange = Worksheets("Tables").Range("e1:f20")
strTemp1 = WorksheetFunction.VLookup(strTemp1, tabRange, 2, False)
else
msgbox("Error : strTemp1 is empty")
end if

End Sub


thomas donino said:
Everything is working fine in this code until after strTemp1 =Left(strTemp1,1),
which = "5"

The Worksheets("Tables").Range("TranslationTable") is a valid table. I
checked by going to Formula>Name manager and checking to see if it was
correct.
But I get a runtime error 1004:
Unable to get the VLookup property of the WorksheetFunction class

I also made sure the cells in the TransactionTable range were set as text so
that a 5 in the cell should be the same as a 5 returned by strTemp1 but that
didn't help either

In the code below I hardcoded the table range to see if that worked but it
did not.

Any suggestions?
Sub TranslateSyntax()

Dim strOne As String, strTwo As String
Dim strFound1 As String, strFound2 As String, strFound3 As String, strFound4
As String
Dim strPart1 As String, strPart2 As String, strPart3 As String, strPart4 As
String
Dim iStrtCol As Integer, iStrtRow As Integer, iEndRow As Integer
Dim EndCol As Integer, NewEndCol As Integer
Dim strTemp1 As String
Dim tabRange As Range

iStrtRow = Cells.Find(What:="when", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="when", MatchCase:=True).Column
iEndRow = Cells(iStrtRow, iStrtCol).End(xlDown).Row

strFound1 = LTrim(Cells(iStrtRow + 1, iStrtCol).Value)
strFound2 = LTrim(Cells(iStrtRow + 3, iStrtCol).Value)
strFound3 = LTrim(Cells(iStrtRow + 5, iStrtCol).Value)

' find the last show day ie 5d = 5 days later
iStrtRow = Cells.Find(What:="show", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="show", MatchCase:=True).Column
For i = iStrtRow To Cells.Find(What:="when", MatchCase:=True).Row - 1

If Cells(, iStrtCol).Value = "" Then
Exit For
Else
strTemp1 = Trim(Cells(, iStrtCol).Value)
strTemp1 = Left(strTemp1, 2)
End If
Next i
strTemp1 = Left(strTemp1, 1)
Set tabRange = Worksheets("Tables").Range("e1:f20")
strTemp1 = WorksheetFunction.VLookup(strTemp1, tabRange, 2, False)


End Sub
Click to expand...
Click to expand...
 
D

Dave Peterson

I would use:
Res as Variant 'could be an error
....then
res = Application.VLookup(strTemp1, tabRange, 2, False)
if iserror(res) then
'no match
else
'a match was found
end if

=========
Saved from a previous post:

There is a difference in the way application.vlookup() and
worksheetfunction.vlookup() (or application.worksheetfunction.vlookup()) behave.

Application.vlookup returns an error that you can check:

dim Res as variant 'could return an error
res = application.vlookup(....)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

Application.worksheetfunction.vlookup raises a trappable error that you have to
catch:

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

(application.match() and application.worksheetfunction.match() behave the same
way.)

Personally, I find using the application.vlookup() syntax easier to read. But
it's personal preference.

thomas said:
Everything is working fine in this code until after strTemp1 =Left(strTemp1,1),
which = "5"

The Worksheets("Tables").Range("TranslationTable") is a valid table. I
checked by going to Formula>Name manager and checking to see if it was
correct.
But I get a runtime error 1004:
Unable to get the VLookup property of the WorksheetFunction class

I also made sure the cells in the TransactionTable range were set as text so
that a 5 in the cell should be the same as a 5 returned by strTemp1 but that
didn't help either

In the code below I hardcoded the table range to see if that worked but it
did not.

Any suggestions?
Sub TranslateSyntax()

Dim strOne As String, strTwo As String
Dim strFound1 As String, strFound2 As String, strFound3 As String, strFound4
As String
Dim strPart1 As String, strPart2 As String, strPart3 As String, strPart4 As
String
Dim iStrtCol As Integer, iStrtRow As Integer, iEndRow As Integer
Dim EndCol As Integer, NewEndCol As Integer
Dim strTemp1 As String
Dim tabRange As Range

iStrtRow = Cells.Find(What:="when", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="when", MatchCase:=True).Column
iEndRow = Cells(iStrtRow, iStrtCol).End(xlDown).Row

strFound1 = LTrim(Cells(iStrtRow + 1, iStrtCol).Value)
strFound2 = LTrim(Cells(iStrtRow + 3, iStrtCol).Value)
strFound3 = LTrim(Cells(iStrtRow + 5, iStrtCol).Value)

' find the last show day ie 5d = 5 days later
iStrtRow = Cells.Find(What:="show", MatchCase:=True).Row
iStrtCol = Cells.Find(What:="show", MatchCase:=True).Column
For i = iStrtRow To Cells.Find(What:="when", MatchCase:=True).Row - 1

If Cells(, iStrtCol).Value = "" Then
Exit For
Else
strTemp1 = Trim(Cells(, iStrtCol).Value)
strTemp1 = Left(strTemp1, 2)
End If
Next i
strTemp1 = Left(strTemp1, 1)
Set tabRange = Worksheets("Tables").Range("e1:f20")
strTemp1 = WorksheetFunction.VLookup(strTemp1, tabRange, 2, False)

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Range With Variables 1
VBA for Dependents shortcut menu 0
InputBox for column letter problem 2
Code does not error but no copy either 4
Ghost excel process 1
Help in sorting data 2
Find the correct sheet then find a value on that sheet 8

Top