Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Vba code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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.




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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.






Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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.








Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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.










Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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.






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 52
Default 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.






.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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.






.



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 493
Default 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.




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
split post code (zip code) out of cell that includes full address Concord Excel Discussion (Misc queries) 4 October 15th 09 06:59 PM
Code to conditional format all black after date specified in code? wx4usa Excel Discussion (Misc queries) 3 December 26th 08 07:06 PM
Drop Down/List w/Code and Definition, only code entered when selec Spiritdancer Excel Worksheet Functions 2 November 2nd 07 03:57 AM
Convert a Number Code to a Text Code Traye Excel Discussion (Misc queries) 3 April 6th 07 09:54 PM
copying vba code to a standard code module 1vagrowr Excel Discussion (Misc queries) 2 November 23rd 05 04:00 PM


All times are GMT +1. The time now is 11:41 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement