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 FormulaName 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([i], iStrtCol).Value = "" Then
Exit For
Else
strTemp1 = Trim(Cells([i], 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

This problem is now solved, with some help.

"thomas donino" wrote:
[i]
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 FormulaName 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([i], 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

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" wrote:
[i]
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 FormulaName 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([i], 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

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

"Joel" wrote:
[i]
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" wrote:
[i]
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 FormulaName 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

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 donino wrote:[i]

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 FormulaName 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([i], 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

--

Dave Peterson