-
1 Attachment(s)
Change column
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
-
How can I get this to work
Code:
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.
-
-
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.
Code:
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.
Code:
IF(LEFT(RC[-6],1)=""1"",""3 Class KKB""
Something is missing from the code below. I get an error each time
Code:
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
-
try
Code:
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
-
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.