Vba code

  • Thread starter Thread starter John
  • Start date Start date
J

John

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
 
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)
 
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
 
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)
 
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
 
Thanks, I missed that.

Bob

Tom Ogilvy said:
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)
Click to expand...
 
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.
 
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.
 
Back
Top