Thread
:
Loop Through Range
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett[_2_]
external usenet poster
Posts: 1,522
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
Reply With Quote
Don Guillett[_2_]
View Public Profile
Find all posts by Don Guillett[_2_]