Formula to avoid compile syntax error

[capnhud]

One of the formulas list whether or not an item is available or not. But when I try create a similar formula to indicate whether the item should be displayed or not I only end up with it being always displayed.

Option Explicit

Sub Reformat()
    Dim wks         As Worksheet
    Dim iRow        As Long

    For Each wks In ActiveWorkbook.Worksheets
        With wks
            'delete extraneous columns
            Columns("X:AQ").Select
            Selection.Delete Shift:=xlToLeft
            Range("A1").Select
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("R:R"), _
                                SortOn:=xlSortOnValues, _
                                Order:=xlAscending, _
                                DataOption:=xlSortNormal
                'additional sort added for more clarity
                .SortFields.Add Key:=Range("A:A"), _
                                SortOn:=xlSortOnValues, _
                                Order:=xlAscending, _
                                DataOption:=xlSortNormal
                .SetRange wks.Range("A:V")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .Apply
            End With

            .Range("C:C,E:E,F:F").Insert Shift:=xlToRight
            Columns("G:G").Select
            Selection.Insert Shift:=xlToRight
            .Range("C1").Value = "sku"
            .Range("E1").Value = "qty"
            .Range("F1").Value = "is_in_stock"
            .Range("G1").Value = "status"
            .Range("I1").Value = "name"
            .Range("K1").Value = "description"
            .Range("M1").Value = "cost"
            .Range("N1").Value = "map"
            .Range("O1").Value = "msrp"
            .Range("Q1").Value = "weight"
            iRow = .Cells.Find(What:="*", _
                               After:=.Range("A1"), _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlPrevious).Row
            .Range("C2:C" & iRow).FormulaR1C1 = "=RC24 & ""-"" & RC1 & ""-"" & RC4"
            .Range("F2:F" & iRow).FormulaR1C1 = "=IF(RC5>=1,""1"",""0"")"
            .Range("G2:G  & iRow).FormulaR1C1 = "=IF(RC6>=1,""Enabled"",""Disabled"")"
            .Range("H2:H" & iRow).FormulaR1C1 = "=RC2 & "" "" & RC8"
        
        With wks.UsedRange
      .Value = .Value
        End With
        
        End With
    Next wks
End Sub

what would be the correct format?

 

[capnhud]

the previous was incorrect

Option Explicit

Sub Reformat()
    Dim wks         As Worksheet
    Dim iRow        As Long

    For Each wks In ActiveWorkbook.Worksheets
        With wks
            'delete extraneous columns
            Columns("X:AQ").Select
            Selection.Delete Shift:=xlToLeft
            Range("A1").Select
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("R:R"), _
                                SortOn:=xlSortOnValues, _
                                Order:=xlAscending, _
                                DataOption:=xlSortNormal
                'additional sort added for more clarity
                .SortFields.Add Key:=Range("A:A"), _
                                SortOn:=xlSortOnValues, _
                                Order:=xlAscending, _
                                DataOption:=xlSortNormal
                .SetRange wks.Range("A:V")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .Apply
            End With

            .Range("C:C,E:E,F:F").Insert Shift:=xlToRight
            Columns("G:G").Select
            Selection.Insert Shift:=xlToRight
            .Range("C1").Value = "sku"
            .Range("E1").Value = "qty"
            .Range("F1").Value = "is_in_stock"
            .Range("G1").Value = "status"
            .Range("I1").Value = "name"
            .Range("K1").Value = "description"
            .Range("M1").Value = "cost"
            .Range("N1").Value = "map"
            .Range("O1").Value = "msrp"
            .Range("Q1").Value = "weight"
            iRow = .Cells.Find(What:="*", _
                               After:=.Range("A1"), _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlPrevious).Row
            .Range("C2:C" & iRow).FormulaR1C1 = "=RC24 & ""-"" & RC1 & ""-"" & RC4"
            .Range("F2:F" & iRow).FormulaR1C1 = "=IF(RC5>=1,""1"",""0"")"
            'causing syntax problem
            '.Range("G2:G  & iRow).FormulaR1C1 = "=IF(RC6>=1,""Enabled"",""Disabled"")"
            .Range("I2:I" & iRow).FormulaR1C1 = "=RC2 & "" "" & RC10"
        
        With wks.UsedRange
      .Value = .Value
        End With
        
        End With
    Next wks
End Sub

 

[Andrew Poulsom]

Is this the cause of the compile error you are referring to?

.Range("G2:G" & iRow).FormulaR1C1 = "=IF(RC6>=1,""Enabled"",""Disabled"")"

 

[capnhud]

yes

the column before it contain either a 0 or 1 and I thought using the previous part of the macro could be applied to the G column

here is an example from csv file

SKURcrd,MfgName,MfgNo,Quantity,smShortDesc,ShortDesc,Description,Picture,Price,MAP,MSRP,AddShip,ShipWeight,ShipLength,ShipWidth,ShipHeight,CategoryParentName,CategoryName,CategoryAbbv,MfgAbbv,MfgSite,ProductGroup,GroupMisc,Rcstus,BarCode,DEPID,RestrictedAreas,Warranty,Thumbnail,ExtraTxt1,ExtraFileNm1,Extra1Exp,ExtraTxt2,ExtraFileNm2,Extra2Exp,ExtraFileNm3,ExtraTxt3,Extra3Exp,Ebay,PicEB,CaseQty,UnitQty,CategoryID
45510,Cold Steel,VDFS,3,The Fighting Sarong DVD,Training DVD,"Ron Balicki will take you through a comprehensive look at the Indonesian and Filipino art of “The Fighting Sarong”. To date this art is relatively unknown to the world. Ron has trained in Sarong fighting for over 20 years and is sharing his vast knowledge with you today. You will learn how to use the Sarong against an attacker who comes at you with punches, kicks and grappling. Learn what to do if your opponent tries to counter you or grab onto your sarong. Ron will also teach you how to use the sarong to defend yourself against a knife. In this series you will also get a special section taught by Martial Arts legend Guro Dan Inosanto.",coldpicvdfs.jpg,50,0,99.99,0,0.6,5.375,1.125,7.625,DVDs & CDs,Safety,VI,COLD,www.coldsteel.com,FZH,Fighting Sarong,C,7.05442E+11,60530,,,th_coldpicvdfs.gif,,,,,,,,,,-1,,2,1,268
46598,Cold Steel,VDFM,0,The Fighting Machete DVD,Training DVD,"Machetes are some of the most common and useful tools in the world. They are used on ranches, farms and orchards or wherever thick brush or dense jungle abound, their long wide blades get the job done. But the Machete is more than just a tool. In a self defense emergency, it can also double as a big knife, or short sword capable of cutting, slashing, smashing or piercing just about anything in its path. In this instructional DVD series, Lynn Thompson teaches you how to harness the full self defense potential of these versatile blades. This video will help you utilize the potential of this common tool in thiis amazing instructional program.",coldpicvdfm.jpg,50,0,99.99,0,1.4,21,4,2,DVDs & CDs,Safety,VI,COLD,www.coldsteel.com,FZH,The Fighting Machete,C,7.05442E+11,61673,,,th_coldpicvdfm.gif,,,,,,,,,,-1,,2,1,268
56738,Cold Steel,VDNU,0,Never Unarmed DVD,Training DVD,"A free man has the inalienable, God given right to defend his person, family, loved ones and property from violent assault, unlawful seizure, confiscation, or trespass. Since firearms are currently at the top of the force continuum it is essential that a free man possess them in abundance and be skilled in their use or he will not long enjoy his precious freedom. Never Unarmed is a very broad look at an enormously wide subject; the firearms and ammunition you need to own or acquire for recreation, hunting, survival, and self-defense. Filmed over 2 1/2 years, it's a HUGE undertaking and features Lynn C. Thompson with the gloves off as he discusses the pros and cons of air guns, .22 handguns, .22 rifles, mini revolvers, pocket pistols, service revolvers, service pistols, big bore handguns, single shot handguns, single shot rifles, double rifles, bolt action rifles, pump action rifles, lever action rifles, semi auto rifles, single and double shotguns, pump action shotguns, and semi automatic shotguns. In format, Never Unarmed will be mostly show and tell, with tactical and training insights from time to time, as well as thought provoking demonstrations regarding the use of cover, concealment, improvised fighting positions, home-made shields and much much more. At times the presentation can become quite graphic as Lynn teaches how to defend yourself against dangerous animals as well as how to neutralize violent human adversaries. Meant for an international audience, it offers lots of options for those who don't enjoy the freedom, security and access to justice we do in the U.S.A. and are already severely restricted in their ability to keep and bear arms. Absolutely chock full of non stop action, graphic demonstrations, and exciting feats of speed, accuracy, and fire power, Never Unarmed is guaranteed to blow you away and change the way you look at and think about firearms forever.",coldpicvdnu.jpg,60,0,119.99,0,0.4,,,,DVDs & CDs,Hunting,VI,COLD,www.coldsteel.com,FZH,Never Unarmed DVD,C,7.05442E+11,73679,,,th_coldpicvdnu.gif,,,,,,,,,,-1,,1,1,264
45511,Cold Steel,VDSC,1,"Stun, Stagger, and Stop DVD",Training DVD,"If you are suddenly attacked, a heavy walking stick or stout cane can be a great comfort. This DVD will give you the necessary skill and confidence necessary to overcome muggers, gang members, and other street lowlifes determined to attack you or members of your family. In This DVD You Will Learn About: - The Power Stroke. - The Fan. - The Drop Stroke. - The Wrap Stroke. - The Flurry. - The Punyo. - The Redondo. - How to Hit Using One or Both Hands. - How to Build Strength, Agility, and Power in Your Strikes - Perfect Your Defense by Learning How to Stop Hit, Counter Slash, Block, Parry,Pass and Shield. - How to Defend Yourself Against an Opponent who’s Trying to Wrest Your Stick Away from You, Using One or Even Both Hands. - Demonstrations of the Multiple Ways to Fight Off an Opponent who Charges and Tries to Grapple You to the Ground. - How to Escape from a Standing Grapple Situation or How to Get Back into the Fight if You are Dragged to the Ground. - Our Strategy and Tactics for Dealing with Multiple Attackers, Edged Weapons, and Blunt Impact Weapons.",coldpicvdsc.jpg,50,0,99.99,0,0.45,5.6375,1.125,7.625,DVDs & CDs,Safety,VI,COLD,www.coldsteel.com,FZH,"Stun, Stagger, Stop",C,7.05442E+11,60531,,,th_coldpicvdsc.gif,,,,,,,,,,-1,,2,1,268

 

[Andrew Poulsom]

The missing double quote (after "G2:G) is causing the compile error.

 

[capnhud]

Thank you that removed the compile error, but unfortunately the formula does not input anything into the column