Vba code
 

Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.

Vba code
 

Hi John,

I think this is what you want

Dim iRow As Long
Dim iCol As Long
Set calls1 = ActiveSheet.Range("V31:AA54")
For iRow = 3 To 5
For iCol = 7 To 9
Set calls2 = ActiveSheet.Cells(iRow, iCol)
Cells(iRow, iCol) = Application.VLookup(calls2, calls1, iRow)
Next iCol
Next iRow


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.

Vba code
 

Sub usingcalls()
Dim col as Variant, i as long, j as Long
Dim calls1 as Range
Set calls1 = ActiveSheet.Range("v31:aa54")
col = Array(0,3,5,6)
for i = 1 to 3 ' row
for j = 1 to 3 ' col
cells(i+2,j+6).Value = _
Application.Vlookup(cells(i+2,6),Calls1,col(j))
Next
Next
End Sub

--
Regards,
Tom Ogilvy


John wrote in message
...
Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.

Vba code
 

Just a heads up, the 3rd argument of Vlookup wasn't sequential.

also
Set calls2 = ActiveSheet.Cells(iRow, iCol)

should be

Set calls2 = ActiveSheet.Cells(iRow, 6)

--
Regards,
Tom Ogilvy

Bob Phillips wrote in message
...
Hi John,

I think this is what you want

Dim iRow As Long
Dim iCol As Long
Set calls1 = ActiveSheet.Range("V31:AA54")
For iRow = 3 To 5
For iCol = 7 To 9
Set calls2 = ActiveSheet.Cells(iRow, iCol)
Cells(iRow, iCol) = Application.VLookup(calls2, calls1, iRow)
Next iCol
Next iRow


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.

Vba code
 

One way that doesn't require any loops:

Public Sub usingcalls()
With Range("G3:I5")
.FormulaR1C1 = _
"=VLOOKUP(RC6,R31C22:R54C27,COLUMN()-4+(COLUMN()7))"
.Value = .Value
End With
End Sub


In article ,
"John" wrote:

Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.

Vba code
 

Thanks, I missed that.

Bob

"Tom Ogilvy" wrote in message
...
Just a heads up, the 3rd argument of Vlookup wasn't sequential.

also
Set calls2 = ActiveSheet.Cells(iRow, iCol)

should be

Set calls2 = ActiveSheet.Cells(iRow, 6)

--
Regards,
Tom Ogilvy

Bob Phillips wrote in message
...
Hi John,

I think this is what you want

Dim iRow As Long
Dim iCol As Long
Set calls1 = ActiveSheet.Range("V31:AA54")
For iRow = 3 To 5
For iCol = 7 To 9
Set calls2 = ActiveSheet.Cells(iRow, iCol)
Cells(iRow, iCol) = Application.VLookup(calls2, calls1,
iRow)
Next iCol
Next iRow


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.

Vba code
 

Tom,

Doesn't j have to start with zero for the array, for the
four elements? Otherwise only the last three elements are
utilized. (Unless changed by declaring it as Option Base
1 or whatever one would like.)

Such that it would be:

for j = 0 to 3, not j = 1 to 3

and then make changes in your following code for the
difference of one as required....

I haven't followed or researched what you all are doing
with this VLookup, but I noticed that. So maybe you've
done it on purpose for some reason. I was just curious.
I use that method (with the arrays) to make all sorts of
complex VBA programming - with loops within loops combined
with arrays and counters - it's a lot of fun. That's why
I noticed it, and looked at it more carefully. Thanks.

-----Original Message-----
Sub usingcalls()
Dim col as Variant, i as long, j as Long
Dim calls1 as Range
Set calls1 = ActiveSheet.Range("v31:aa54")
col = Array(0,3,5,6)
for i = 1 to 3 ' row
for j = 1 to 3 ' col
cells(i+2,j+6).Value = _
Application.Vlookup(cells(i+2,6),Calls1,col(j))
Next
Next
End Sub

--
Regards,
Tom Ogilvy


John wrote in message
...
Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.






.

Vba code
 

No, I don't need the zero value/zero index. Thus I start with the second
element (index of 1, value of 3). I did assume option base 0 since that is
the default.

No adjustments need to be made given my assumptions -- which I guess I
should have stated.

--
Regards,
Tom Ogilvy

Rick wrote in message
...
Tom,

Doesn't j have to start with zero for the array, for the
four elements? Otherwise only the last three elements are
utilized. (Unless changed by declaring it as Option Base
1 or whatever one would like.)

Such that it would be:

for j = 0 to 3, not j = 1 to 3

and then make changes in your following code for the
difference of one as required....

I haven't followed or researched what you all are doing
with this VLookup, but I noticed that. So maybe you've
done it on purpose for some reason. I was just curious.
I use that method (with the arrays) to make all sorts of
complex VBA programming - with loops within loops combined
with arrays and counters - it's a lot of fun. That's why
I noticed it, and looked at it more carefully. Thanks.

-----Original Message-----
Sub usingcalls()
Dim col as Variant, i as long, j as Long
Dim calls1 as Range
Set calls1 = ActiveSheet.Range("v31:aa54")
col = Array(0,3,5,6)
for i = 1 to 3 ' row
for j = 1 to 3 ' col
cells(i+2,j+6).Value = _
Application.Vlookup(cells(i+2,6),Calls1,col(j))
Next
Next
End Sub

--
Regards,
Tom Ogilvy


John wrote in message
...
Hello,
I have a following code, which I would like to
make compact using something like
'Dim iRow As Long
'Dim icol As Long
For iRow = 3 To 5
For icol = g To i
---------------------------
Sub usingcalls()
Set calls1 = ActiveSheet.Range("v31:aa54")
Set calls2 = ActiveSheet.Range("f3")
Set calls3 = ActiveSheet.Range("f4")
Set calls4 = ActiveSheet.Range("f5")

Range("g3") = Application.VLookup(calls2, calls1, 3)
Range("h3") = Application.VLookup(calls2, calls1, 5)
Range("i3") = Application.VLookup(calls2, calls1, 6)
Range("g4") = Application.VLookup(calls3, calls1, 3)
Range("h4") = Application.VLookup(calls3, calls1, 5)
Range("i4") = Application.VLookup(calls3, calls1, 6)
Range("g5") = Application.VLookup(calls4, calls1, 3)
Range("h5") = Application.VLookup(calls4, calls1, 5)
Range("i5") = Application.VLookup(calls4, calls1, 6)

End Sub
----------------------------------
I am new to this vba thing.Trying to learn.

Thanks.






.