Consulting

Results 1 to 6 of 6

Thread: Change column

  1. #1

    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
    Attached Files Attached Files
    Last edited by joeny0706; 01-17-2021 at 09:50 PM.

  2. #2
    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.
    Last edited by Paul_Hossler; 01-18-2021 at 05:09 AM.

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Please use code tags !

  4. #4
    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

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •