formula - vba macro

[russlock]

Hi, not sure if anyone can help but i am basically trying to add a fixed percentage to an existing price (Column G) on a multitude of spreadsheets with each one representing each customer.
i did record a macro and inserted the code into a previous script that Alex amended for me from "TheSpreadsheetGuru.com"
however, the amount of rows with be different on every customer as one customer will buy more items than the other so will have more rows. when I did the macro, the customer customer had 138 rows which I can see in the below script, but anyone who has more rows does not get changed and any with less just end up with a zero.

I need the script to just find the last item in column "G" and to amend the price and move on to the next spreadsheet in the folder.

Hope all the above makes sense.

Many thanks for any help offered.

====================

Sub testIncrease()
'
' testIncrease Macro
'

'

    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'my recorded macro code here
'
' Add 6% to ccp prices
'
'
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("G:G").Select
    Selection.NumberFormat = "General"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:G1"), Type:=xlFillDefault
    Range("F1:G1").Select
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*(1+6%)"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G138")
    Range("G2:G138").Select
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
   
    'end of my recorded macro code
            End With
           
                    'Save and Close Workbook
           'Saving the Workbook
ActiveWorkbook.Save
ActiveWorkbook.Close
        
        'Ensure Workbook has closed before moving on to next line of code
          DoEvents
  
        'Get next file name
            xFileName = Dir
           
        Loop
    End If
   
        'Message Box when tasks are completed
      MsgBox "Task Complete!"
  
ResetSettings:
      'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
End Sub

 

[Micron]

I'm new at this but the usual method of finding the last used row in a column seems to be .Cells(Rows.count, "G").End(xlUp).Row
You'll want to preface the leading dot (.) with a sheet reference of some kind. Could be ActiveSheet (can be unpredictable) or Sheets("mySheetName") or the like.

 

[russlock]

I'm new at this but the usual method of finding the last used row in a column seems to be .Cells(Rows.count, "G").End(xlUp).Row
You'll want to preface the leading dot (.) with a sheet reference of some kind. Could be ActiveSheet (can be unpredictable) or Sheets("mySheetName") or the like.

not sure where that line goes, but i will have a play. Thanks

 

[Micron]

I'd say anywhere that you're using a static range reference, such as here: ("G2:G138")
You can use what I posted to return a number (e.g. 138) to a variable (e.g. lngRow) then instead write ("G2:G" & lngRow). Would probably be easier to read than
("G2:G" & Sheets("Sheet2").Cells(Rows.count, "G").End(xlUp).Row) but either should work.

 

[russlock]

Unfortunately, I think VBA is way way over my head as I can't get this to work. Many thanks though, much appreciated for your time.

 

[Micron]

Willing to stick with it some more if you are but you'd need to explain "can't get this to work". Means syntax error message? Run time error message? If either, which line? No message but does not do what you expect? Would also be a good idea to post your new code.

 

[russlock]

many thanks for your time.

Just to give you an idea of what I am doing if this helps. I have a large number of customer price lists and I need to implement a set price increase of 6% to all of the items on each workbook. I know all the code works as it asks me to choose the folder that all the workbooks are in, and it then runs through the macro I recorded on one of the customer workbooks and then copied into the VBA.

It is this that fails for me as it correctly adds the 6% to every workbook, but only goes to line G138 which was the last item on the first workbook that I recorded the macro on. Every other workbook has different amount of rows and it then adds a "0" on that line which won't work as I need to import back into our system and the "0" are causing an issue. I need the script to find the last row in column G and close that workbook and move onto the next.

I really am no good at VBA so tried to insert your code (which looked like the below from a different script that does work) within this macro I did as below. but instantly hit an error when the script ran.
this is what i was inserting,

destLastRow = destSht.Cells(Rows.Count, "G").End(xlUp).Row

but I guess a very very small bit of knowledge on my side is a dangerous thing. (good job I don't dabble with electricity )
i have added an image of what it looks like after the script has ran

many thanks.

' Add 6% to ccp prices
'
'
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("G:G").Select
    Selection.NumberFormat = "General"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:G1"), Type:=xlFillDefault
    Range("F1:G1").Select
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*(1+6%)"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G138")
    Range("G2:G138").Select
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    
    'end of my recorded macro code

 

[Micron]

It looks like the same code as before. I don't see where you used the attempt that you posted outside of the code. I take it that you want the formula to autofill from G2 to the last used row?

 

[russlock]

It looks like the same code as before. I don't see where you used the attempt that you posted outside of the code. I take it that you want the formula to autofill from G2 to the last used row?

That is correct yes. Thanks

 

[Micron]

I'm almost there, but I think I made a boo-boo in my last post. You want G to autofill based on how many rows are in F, not G, correct?
One other thing I don't understand: if the formulas in G rows present a calculation based on what's in F rows, what good are the G formulas if you delete F?
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft <<?

Going to look at that some more but I have to finish editing this; time's almost up.
quick edit - I think I see. Paste special should paste values over formula. Not working for me yet.