Create a Macro to Insert a number into a column based on criteria
 

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 !!!!!

Create a Macro to Insert a number into a column based on criteria
 

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


" 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 !!!!!

Create a Macro to Insert a number into a column based on criteria
 

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


" 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 !!!!!

Create a Macro to Insert a number into a column based on crite
 

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


" 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


" 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 !!!!!