Consulting

Results 1 to 3 of 3

Thread: Qucik help

  1. #1

    Qucik help

    Hi All


    I have a module that is working good. But there is another piece I need to add and I have something small causing an issue but cant figure it out.
    This is the one I have that works well.

    SubaddClassAll()
      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 RouteBread"", IF(LEFT(RC[-7],1)=""3"",""5Restaurants"",IF(LEFT(RC[-7],1)=""4"",""1Route Bread"", """")))"
    If Where.Rows.Count > 1 Then
    .AutoFill Destination:=Where,Type:=xlFillDefault
    End If
    End With
    Next
    End Sub

    It looks at column C and if it starts with a 1 it needs to put "1 Route Bread" in column J or it it starts with a 3 puts "5 restaurants" or if it is a 4 "1 route bread".

    I now need to add if column C starts with a 2 I need it to fill column J with "6Institutional" also. I wrote this but it is not working.

    SubaddClassAll()
    Dim sht As Worksheet
      DimWhere 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 RouteBread"", IF(LEFT(RC[-7],1)=""2"",""6Institutional"", IF(LEFT(RC[-7],1)=""3"",""5Restaurants"",IF(LEFT(RC[-7],1)=""4"",""1 RouteBread"", """")))"
    If Where.Rows.Count > 1 Then
            .AutoFill Destination:=Where,Type:=xlFillDefault
    End If
        End With
      Next
    End Sub



    Anyone haveany thoughts why it keeps giving me an error

    Thanks All
    Last edited by Aussiebear; 04-05-2019 at 03:59 PM. Reason: tidied up the thread

  2. #2
    Was just missing ) at end. After starting so long my eyes stopped working

    Thanks anyways

  3. #3
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello joeny0706,

    A simpler formula would be... =IFERROR(CHOOSE(LEFT($A1,1),"1 Route Bread","6 Institutional","5 Restaurants","1 Route Bread"),"")
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

Posting Permissions

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