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

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

That did what I needed

Thank you so much Mike

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

Your Welcome

"PAG" wrote:

That did what I needed

Thank you so much Mike

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

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

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

PAG,
Rather than "hard code" your searches, you could prompt the user with an
InputBox to enter the value being searched for. Following suggested code
prompts user for a numeric value & then will search Col B only €“ if found it
adds the data you require to Col E.

Sub FindValue()
Dim FoundCell As Range
Dim myws As Worksheet
Dim Search As Single
Dim MyTitle As String

MyTitle = "Search Number"
Set myws = Worksheets("Sheet1") '<< change as required

Search = Application.InputBox(prompt:="Enter Number To Find",
Title:=MyTitle, Type:=1)
If Search < 0 Then
Set FoundCell = myws.Columns("B").Find _
(Search, LookIn:=xlValues, LookAt:=xlWhole)

If FoundCell Is Nothing = False Then
FoundCell.Offset(0, 3).Value = "European Trade"
Else
msg = MsgBox("Value " & Search & " Not Found", vbInformation,
MyTitle)
End If
End If
End Sub

You will note that I have referenced a worksheet named €œSheet1€� you will
need to amend this to name of sheet you are searching.

Hope Useful

--
JB


"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

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