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

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]

Worked a treat, thanks.


Cheers

I wish I had a brain