About Us

PAG

Thank You again

"Mike" wrote:

Try this
Sub findValue()
Const whatColumn = "B" 'Change to your needs
Dim i As Long ' i = 5 Starting row of data change to your needs
Dim lastRow As Long
Dim cellPointer As Variant

lastRow = Range(whatColumn & Rows.Count).End(xlUp).Row

For i = 5 To lastRow
Set cellPointer = Worksheets("Sheet1").Cells(i, 2)
If cellPointer = 42285 Then
cellPointer.Offset(0, 3).Value = "European Trade"
End If
If cellPointer = 9992 Then
cellPointer.Offset(0, 3).Value = "Dummy fund"
End If
Next i
End Sub

"PAG" wrote:

Mike,

One more question. How would I add other search criteria to the code? Such
as also searching for "9992" to return the value "Dummy Fund".

Thanks,
Peter

"Mike" wrote:

Try this
Sub findValue()
Const whatColumn = "B" 'Change to your needs
Dim i As Long 'i = 5 Starting row of data change to your needs
Dim lastRow As Long
Dim cellPointer As Variant

lastRow = Range(whatColumn & Rows.Count).End(xlUp).Row

For i = 5 To lastRow
Set cellPointer = Worksheets("Sheet1").Cells(i, 2)
If cellPointer = 42285 Then
cellPointer.Offset(0, 3).Value = "European Trade"
End If
Next i
End Sub

"PAG" wrote:

Hi

Can someone please help me create a macro to search an Excel worksheet(in
column B) for a cell value("42285") and return a statement("European Trade")
in column "E". The data can always change and the rows of data can be up to
3,000 rows.

I have the below code but it needs alot of work.

Dim rowCount As Integer
rowCount = ActiveSheet.UsedRange.Rows.Count

Dim i As Integer

For i = 1 To rowCount
Range("B" & i).Select
If ActiveCell.Value = "42285" Then
GoTo loopAgain
Else

ActiveCell.Offset(0, 4) = Sheets("Sheet1").ActiveCell.Offset(6, 4)

' write code to do whatever(return your activecell.offset value)

End If

loopAgain:
Next i

End Sub

Thank You
Peter

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find First Non blank cell than find column header and return that value	Silver Rose	Excel Worksheet Functions	10	April 30th 07 05:56 PM
Return blank cell if 'find' statement not true	Kanga 85	Excel Worksheet Functions	4	May 28th 06 04:25 PM
Find certain text in a column and return statement	TelecomAuditor	Excel Worksheet Functions	4	August 22nd 05 11:00 PM
Creating a macro to find duplicate names	Carter Devereaux	Excel Discussion (Misc queries)	1	July 6th 05 10:39 PM
macro to find a text and return a message	jane	Excel Programming	0	January 22nd 04 01:56 PM

Menu

About Us