Loop Through Range

N

newguy

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
 
D

Don Guillett

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top