For...Next loop problem!!

[mellowe]

Hi All

I have a macro to look at cell H44, check the first 4 characters then
depending on the value populate cell O44 with a value 1-5 and do the
same down to O900.

The problem I have is only cell H44 is being checked and the same
result is being put in Cells O44:O900, but I wanted H44 checked then
result in O44, then H45 checked result in O45 etc right down to O900.

I know there must be something simple missing from my loop but I cant
see what?

Please Help!! - below is my macro:

Dim BreakComment As String
Dim MyStr As String
Dim StartRowD As Long
Dim MCell As Range

With ActiveSheet
StartRowD = 44

For Each MCell In ActiveSheet.Range("O44:O900").Cells

BreakComment = Range("H44").Value
MyStr = Left(BreakComment, 4) ' Returns first 4 char of string


If MyStr = "FTR0" _
Or MyStr = "SLD1" _
Or MyStr = "SEG0" _
Or MyStr = "FRAC" _
Or MyStr = "LSA1" _
Or MyStr = "IPB " _
Or MyStr = "REPO" _
Or MyStr = "IPBF" _
Or MyStr = "FIDA" Then
MCell = 1

ElseIf MyStr = "IPB3" _
Or MyStr = "SLD1" _
Or MyStr = "SLD2" _
Or MyStr = "SLD3" _
Or MyStr = "CORP" _
Or MyStr = "IMMB" _
Or MyStr = "TSE1" _
Or MyStr = "PWM1" _
Or MyStr = "OMNI" _
Or MyStr = "SPS0" _
Or MyStr = "DRIP" _
Or MyStr = "TIME" Then
MCell = 2

ElseIf MyStr = "IPB1" _
Or MyStr = "IPB2" _
Or MyStr = "TSWD" _
Or MyStr = "PLUG" _
Or MyStr = "WDEP" Then
MCell = 3

ElseIf MyStr = "IPB4" _
Or "TBT3" _
Or "UNC1" Then
MCell = 4

ElseIf MyStr = "FSAC" Then
MCell = 5

Else
MCell = 0

End If

Next MCell

End With

End Sub

 

[Ardus Petus]

See update in your code

HTH
--
AP

Hi All

I have a macro to look at cell H44, check the first 4 characters then
depending on the value populate cell O44 with a value 1-5 and do the
same down to O900.

The problem I have is only cell H44 is being checked and the same
result is being put in Cells O44:O900, but I wanted H44 checked then
result in O44, then H45 checked result in O45 etc right down to O900.

I know there must be something simple missing from my loop but I cant
see what?

Please Help!! - below is my macro:

Dim BreakComment As String
Dim MyStr As String
Dim StartRowD As Long
Dim MCell As Range

With ActiveSheet
StartRowD = 44

For Each MCell In ActiveSheet.Range("O44:O900").Cells

BreakComment = Range("H44").Value

**** Should be:
BreakComment = Cells(mcell.row,"H")
****

 

[Bob Phillips]

why not just


BreakComment = mcell.Value

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mellowe]

Thanks for the replies...
BreakComment = Cells(mcell.row,"H") worked great..thanks

dont think BreakComment = mcell.Value worked Bob as
Mcell is refering to the Col "O" value and the BreakComment is refering
to "H" value
If BreakComment was set to Mcell value would it not set the values in
col H to values in col O

 

[mellowe]

Thanks for the replies...
BreakComment = Cells(mcell.row,"H") worked great..thanks

dont think BreakComment = mcell.Value worked Bob as
Mcell is refering to the Col "O" value and the BreakComment is refering
to "H" value
If BreakComment was set to Mcell value would it not set the values in
col H to values in col O

 

[Bob Phillips]

Yeah, sorry missed that bit.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)