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