Hello,

I so used to working with Access that I'm having trouble trying how to
write this simple macro I need:

I want to create a macro that says if column A has "016" then insert
"234" into Column B. I need to do mulitple of these, so that was just
an example. I'm in a time crunch at work. Thanks a million in
advance !!!!!


(E-Mail Removed)

Here is one way using find...

Public Sub AddStuff()
Call FindStuff(Columns("A"), "016", "234")
Call FindStuff(Columns("A"), "This", "That")
End Sub

Private Sub FindStuff(ByVal rngToSearch As Range, ByVal strWhat As String, _
ByVal strValue As String)
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set rngFound = rngToSearch.Find(What:=strWhat, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Offset(0, 1).Value = strValue
End If
End Sub
--
HTH...

Jim Thomlinson


"(E-Mail Removed)" wrote:

> Hello,
>
> I so used to working with Access that I'm having trouble trying how to
> write this simple macro I need:
>
> I want to create a macro that says if column A has "016" then insert
> "234" into Column B. I need to do mulitple of these, so that was just
> an example. I'm in a time crunch at work. Thanks a million in
> advance !!!!!
>
>
> (E-Mail Removed)
>
>

I proably have overlooked it, but where is column B referenced. I want
to insert 234 on every line (row) in column B where column A's line
(row) equals 016. This occurs multiple times in the worksheet, not
just one instance. If it does that, please forgive me. Thanks again



Jim Thomlinson wrote:
> Here is one way using find...
>
> Public Sub AddStuff()
> Call FindStuff(Columns("A"), "016", "234")
> Call FindStuff(Columns("A"), "This", "That")
> End Sub
>
> Private Sub FindStuff(ByVal rngToSearch As Range, ByVal strWhat As String, _
> ByVal strValue As String)
> Dim rngFound As Range
> Dim rngFoundAll As Range
> Dim strFirstAddress As String
>
> Set rngFound = rngToSearch.Find(What:=strWhat, _
> LookAt:=xlWhole, _
> LookIn:=xlValues, _
> MatchCase:=False)
> If Not rngFound Is Nothing Then
> strFirstAddress = rngFound.Address
> Set rngFoundAll = rngFound
> Do
> Set rngFoundAll = Union(rngFound, rngFoundAll)
> Set rngFound = rngToSearch.FindNext(rngFound)
> Loop Until rngFound.Address = strFirstAddress
> rngFoundAll.Offset(0, 1).Value = strValue
> End If
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "(E-Mail Removed)" wrote:
>
> > Hello,
> >
> > I so used to working with Access that I'm having trouble trying how to
> > write this simple macro I need:
> >
> > I want to create a macro that says if column A has "016" then insert
> > "234" into Column B. I need to do mulitple of these, so that was just
> > an example. I'm in a time crunch at work. Thanks a million in
> > advance !!!!!
> >
> >
> > (E-Mail Removed)
> >
> >

Column B is the offset of one column from A. Here is the line that does that.
rngFoundAll.Offset(0, 1).Value = strValue
What this code does is it seaches Column A for all instances (rngFoundAll)
of "016" or whatever you set as the second argument (strWhat). It then places
"234" (strValue) one column to the right of the items that it found.

It works with multiple instances and is very efficient in how it searches...
--
HTH...

Jim Thomlinson


"(E-Mail Removed)" wrote:

> I proably have overlooked it, but where is column B referenced. I want
> to insert 234 on every line (row) in column B where column A's line
> (row) equals 016. This occurs multiple times in the worksheet, not
> just one instance. If it does that, please forgive me. Thanks again
>
>
>
> Jim Thomlinson wrote:
> > Here is one way using find...
> >
> > Public Sub AddStuff()
> > Call FindStuff(Columns("A"), "016", "234")
> > Call FindStuff(Columns("A"), "This", "That")
> > End Sub
> >
> > Private Sub FindStuff(ByVal rngToSearch As Range, ByVal strWhat As String, _
> > ByVal strValue As String)
> > Dim rngFound As Range
> > Dim rngFoundAll As Range
> > Dim strFirstAddress As String
> >
> > Set rngFound = rngToSearch.Find(What:=strWhat, _
> > LookAt:=xlWhole, _
> > LookIn:=xlValues, _
> > MatchCase:=False)
> > If Not rngFound Is Nothing Then
> > strFirstAddress = rngFound.Address
> > Set rngFoundAll = rngFound
> > Do
> > Set rngFoundAll = Union(rngFound, rngFoundAll)
> > Set rngFound = rngToSearch.FindNext(rngFound)
> > Loop Until rngFound.Address = strFirstAddress
> > rngFoundAll.Offset(0, 1).Value = strValue
> > End If
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> > > Hello,
> > >
> > > I so used to working with Access that I'm having trouble trying how to
> > > write this simple macro I need:
> > >
> > > I want to create a macro that says if column A has "016" then insert
> > > "234" into Column B. I need to do mulitple of these, so that was just
> > > an example. I'm in a time crunch at work. Thanks a million in
> > > advance !!!!!
> > >
> > >
> > > (E-Mail Removed)
> > >
> > >
>
>