joeny0706
01-17-2021, 09:30 PM
I have the code below that looks in column C and depending on what the data starts with "1,2,3 or 5" it add a string in column J.
I need to edit this so it looks in column D. If the text in column D starts with a 1 it needs to put "3 Class KKB" in column J.
I have been playing with it and no luck yet, This was used for something more complex but I thought I would be able to edit this my self. I have not had luck yet. I will keep playing.
Really all I need is it to search column D and if it starts with a 1 to put "3 Class KKB" in the same row in column J.
If anyone wants to help out I would appreciate it
Sub addClassAll()
Dim sht As Worksheet
Dim Where As Range
For Each sht In Worksheets
With sht
Set Where = .Range("C" & .Rows.Count).End(xlUp)
Set Where = .Range("J1", .Range("J" & Where.Row))
End With
With sht.Range("J1")
.FormulaR1C1 = _
"=IF(LEFT(RC[-7],1)=""1"",""1 Sales"",IF(LEFT(RC[-7],1)=""3"",""5 Rest"",IF(LEFT(RC[-7],1)=""2"",""6 Institute"",IF(LEFT(RC[-7],1)=""4"",""1 Sales"", """"))))"
If Where.Rows.Count > 1 Then
.AutoFill Destination:=Where, Type:=xlFillDefault
End If
End With
Next
End Sub
I did attach the module in the zip file.
I was playing and figured out if I change the -7 to -6 it looks in column D. So rather than making a new module I can add another section but I am unable to get it. I know what I need just cant get it to go
IF(LEFT(RC[-6],1)=""1"",""3 Class KKB"" I cant add that within and get it to run. Help please
I need to edit this so it looks in column D. If the text in column D starts with a 1 it needs to put "3 Class KKB" in column J.
I have been playing with it and no luck yet, This was used for something more complex but I thought I would be able to edit this my self. I have not had luck yet. I will keep playing.
Really all I need is it to search column D and if it starts with a 1 to put "3 Class KKB" in the same row in column J.
If anyone wants to help out I would appreciate it
Sub addClassAll()
Dim sht As Worksheet
Dim Where As Range
For Each sht In Worksheets
With sht
Set Where = .Range("C" & .Rows.Count).End(xlUp)
Set Where = .Range("J1", .Range("J" & Where.Row))
End With
With sht.Range("J1")
.FormulaR1C1 = _
"=IF(LEFT(RC[-7],1)=""1"",""1 Sales"",IF(LEFT(RC[-7],1)=""3"",""5 Rest"",IF(LEFT(RC[-7],1)=""2"",""6 Institute"",IF(LEFT(RC[-7],1)=""4"",""1 Sales"", """"))))"
If Where.Rows.Count > 1 Then
.AutoFill Destination:=Where, Type:=xlFillDefault
End If
End With
Next
End Sub
I did attach the module in the zip file.
I was playing and figured out if I change the -7 to -6 it looks in column D. So rather than making a new module I can add another section but I am unable to get it. I know what I need just cant get it to go
IF(LEFT(RC[-6],1)=""1"",""3 Class KKB"" I cant add that within and get it to run. Help please