Change column

[joeny0706]

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

[joeny0706]

How can I get this to work



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[-6],1)=""1"",""3 Class KKB"",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





Ok I dont think I was right. So back to the first task.

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.

How ever I can get that done. The current module I use is attached to the first post. Either edit that one or create a new one. Both will be ok for me

Thanks




One other thing I dont like about this module is that it leaves the code in the cell. If I click in column j rather than see the text I see the code. If this can be made so only the text is left in the column not the code that would be great. That is a secondary task. It works fine as is just something I wanted to add.

[snb]

Please use code tags !

[joeny0706]

I am getting close. After having more time and adjusting the current code I am using I am able to get it to work by changing that to a -6. I jsut cant figure out how to add a new section within. Each time I try I get error. I am missing either ) or " I think.



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 need to add this within the top code.



IF(LEFT(RC[-6],1)=""1"",""3 Class KKB""


Something is missing from the code below. I get an error each time




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[-6],1)=""1"",""3 Class KKB"",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

[p45cal]

try
Sub addClassAll()
Dim sht As Worksheet
For Each sht In Worksheets
sht.Range("J1:J" & sht.Range("C" & sht.Rows.Count).End(xlUp).Row).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[-6],1)=""1"",""3 Class KKB"",IF(LEFT(RC[-7],1)=""4"",""1 Sales"", """")))))"
Next
End Sub

[joeny0706]

That is very close. But for some reason it only worked correctly when I had the 3 Class KKB first. It cause issues if it was after the other statements.

I did get it working. Thanks for the help all.