joeny0706
04-05-2019, 07:28 AM
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
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