VBA Finding the next empty cell

I apologise I am starting to learn VBA – very slowly!

My proble revolves around two worksheets: “Front Page” and “Durations”.  On “Front Page” a drop down box provides 49 options.  Each option ‘triggers’ an array of up to 10 rows of values and provides a total of the values (all the Values are formatting in TIME).  I want, in turn, to select each of the 49 options and copy the total and paste each value in column A in turn – thus providing an array of 49 totals.  I have tried to creat a Macro but whilst it partially works, it won’t completely.  The part that works is that the current total is copied (Cell G23 on “Front Page”) and then recorded in Cell A1 in “Durations”.  If I then select another option and run the Macro, the new total replaces the value in Cell A1.  I have tried two ways of doing that:

Sheets(“Front Page”).Activate

Range (“G32”). Select

Selection.Copy

Sheets(“Durations”).Select

NextRow=Application.WorksheetFunction.CountA(Range(“A:A))+1

Cells(NextRow, 1) = Range(“G32”)

Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets(“Front Page”).Select

As an alternative in the centre of the Macro I tried:

Dim BlankRow As Long

BlankRow = Range(“A65536”).End(xlUp).Row +1

Cells(BlankRow, 1) =Range(“G32”)

I want ultimately the Macro to extend when triggered to go through each of the 49 options ‘automatically’ to record each total in turn but that is for the future.  At the moment I have no idea how to trigger the options in turn but the question is about what mistake I am making which prevents it to ‘NextRow’.

Help would be appreciated  Roger

By: Roger Gaspar

Leave a Reply

Your email address will not be published. Required fields are marked *