check column -A- and fill out column -B- by vba

[MicrosoftNews]

Hi all,

please help me by solving the folowing problem...
I want to check a sheet with four columns
---------------------------------------------------
-A- -B- -C- -D-
123 active 1,2 3,0
456 hold 2,4 4,2
789 clear 5,7 3,6
123 3,9 2,4
123 1,7 8,4
789 9,4 4,3
456 4,3 2,8
---------------------------------------------------

now I want to check column -A- for "123"and fill the column -B- with the
value "active" for the following rows.

How can I do this by vba ?????

Many thx in advance.

greetings joachim

 

[Gary''s Student]

Sub surface()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
If Cells(i, 1).Value = 123 Then
Cells(i, 2).Value = "active"
End If
Next
End Sub

 

[ilia]

Try this. Insert the code below into a standard module of the
workbook you're working with. Replace the constants at the top to
suit your needs (I tried to make it more broad than you've outlined).
Hopefully it's self-explanatory.

Public Sub fillAdjacentColumn()
Const sheetName As String = "Sheet1"
Const columnToLookIn As String = "A:A"
Const lookFor As String = "123"
Const fillWith As String = "active"
Const columnsToTheRight As Integer = 1

Dim rngA As Excel.Range
Dim rng As Excel.Range
Dim wsh As Excel.Worksheet

Set wsh = ThisWorkbook.Worksheets(sheetName)

With wsh
Set rngA = Intersect( _
.Range(columnToLookIn), _
.UsedRange)
End With

For Each rng In rngA.Cells
If rng.Value = lookFor Then
rng.Offset(0, _
columnsToTheRight).Value = fillWith
End If
Next rng
End Sub

 

[Gord Dibben]

Do you also want the next 456 to receive "hold" in column B and the next 789 to
receive "clear" in column B.


Gord Dibben MS Excel MVP