cerbera
|
| posted on 8/7/11 at 04:26 PM |
|
|
Calling Excel VB Wizards
I have done a script where at the beginning the user is prompted to enter a value (eg 'LCB' ). This value is then written to a cell, lets
say cell aa1.
Later in the script I want to use this value (LCB) and find the same value (LCB) on a given row.
The following code looks on sheet 2 along row A1:Z1 for the word 'Quantity'. It then copies the column and pastes it to sheet 1. This
works fine.
Sub FindAddressColumn3()
Sheets("Sheet2" ).Select
Dim rngAddress As Range
Set rngAddress = Range("A1:Z1" ).Find("Quantity" )
Range(rngAddress, rngAddress.EntireColumn).Copy
Sheets("Sheet1" ).Select
Range("C1:C2500" ).Select
Selection.PasteSpecial
End Sub
This code is the same as the last except I want it to look at cell aa1 (which contains LCB) and search row A2:Z2 for it.
Sub FindAddressColumn4()
Sheets("Sheet2" ).Select
Dim rngAddress As Range
Set rngAddress = Range("A2:Z2" ).Find(aa1)
Range(rngAddress, rngAddress.EntireColumn ).Copy
Sheets("Sheet1" ).Select
Range("D1: D2500" ).Select
Selection.PasteSpecial
End Sub
Cheers in advance 
|
|
|
|
|
Minicooper
|
| posted on 8/7/11 at 04:41 PM |
|
|
cellValue = Sheets("sheet2" ).Range("aa1" ).Value
Set rngAddress = Range("A2:Z2" ).Find(cellValue)
or you can put it all in one line, if the value is not there it will fail so you may want to test for that
How odd it keeps putting a smiley face where there are close brackets??
Cheers
David
[Edited on 8/7/11 by Minicooper]
|
|
|
cerbera
|
| posted on 8/7/11 at 04:51 PM |
|
|
Worked a treat, thanks.
Cheers 
|
|
|
Mark Allanson
|
| posted on 8/7/11 at 05:39 PM |
|
|
I wish I had a brain
If you can keep you head, whilst all others around you are losing theirs, you are not fully aware of the situation
|
|
|
