Loop Through Range
 

I am trying to loop through a range and if the first number in the
current cell in that range is between 1 and 8 I want to concationate
it with a value in another cell then past it in a cell 4 rows to the
left. Below is what I trying to get to work thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Dim AcctRange As Range
Dim CellTest As Variant


Set AcctRange = Range("B5:B250")

For Each CellTest In AcctRange.Cells
If CellTest.Value(Left(CellTest.Value, 1) 3) And
CellTest.Value(Left(CellTest.Value, 1) < 8) Then
Set AcctRange.Offset(4, 0).Value = (Cells(1,
5).Value & CellTest.Value)
Else
Set AcctRange.Offset(, 4).Value = CellTest.Value

End If
Next

End Sub

Loop Through Range
 

I don't know why you would want this within a worksheet_change event.. If
you do want it to change with each entry in the range then it could be in
the event but written differently. Post back if this is your need.
Sub trythis()
dim c as range
For Each c In Range("b5:b250")
If Left(c, 1) 3 And Left(c, 1) < 8 Then
c.Offset(,-4) = Range("a5") & c
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"newguy" wrote in message
...
I am trying to loop through a range and if the first number in the
current cell in that range is between 1 and 8 I want to concationate
it with a value in another cell then past it in a cell 4 rows to the
left. Below is what I trying to get to work thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Dim AcctRange As Range
Dim CellTest As Variant


Set AcctRange = Range("B5:B250")

For Each CellTest In AcctRange.Cells
If CellTest.Value(Left(CellTest.Value, 1) 3) And
CellTest.Value(Left(CellTest.Value, 1) < 8) Then
Set AcctRange.Offset(4, 0).Value = (Cells(1,
5).Value & CellTest.Value)
Else
Set AcctRange.Offset(, 4).Value = CellTest.Value

End If
Next

End Sub