The method for using a cell value for a variable cell position - VBA

Charles_P

New Member
Joined
Mar 23, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Preface: I am very new to VBA, so try not to laugh at my code too much.

I have a reporting worksheet that gets populated with a day's events, and generates statistics based on those entries. Each day the report is duplicated and formatted ready to be populated with the new day's events. The statistics from the previous day's report need to be copied and pasted from a the previous day's "Total" cells to the new day's reports "previous" cells to create a running total. This would be a simple task, but unfortunately, the location of these cells are dependant on how many entries (Rows) have been added.

I have managed to write a very janky code that locates the required cells, copies them and pastes them into the correct new cells. My current code moves each cell's value individually and although it works, it is very inefficient.
I my first attempt at this code was "more tidy", but it fails at the last hurdle, as it just references the cell, rather than the value (cell position) it contains.

My inefficient working code is:
VBA Code:
'Find Reference cell
Range("AD1").Formula = "=MATCH(""STOP_HERE"",C:C,0)" 'This formula finds the Reference cell (gray row)
'Copy Mob&Demob hours to date
    Range("AE1").Formula = "= AD1 +7"
    Range("AF1").Formula = "= AD1 +8"
    Range("AG1").Formula = "= AD1 +9"
    Range("AH1").Formula = "= AD1 +10"
    Range("AI1").Formula = "= AD1 +11"
    Range("AJ1").Formula = "= AD1 +12"
    
    Dim var1 As Integer
    Dim var2 As Integer
    Dim var3 As Integer
    Dim var4 As Integer
    Dim var5 As Integer
    Dim var6 As Integer
    
    var1 = Range("AE1").Value
    var2 = Range("AF1").Value
    var3 = Range("AG1").Value
    var4 = Range("AH1").Value
    var5 = Range("AI1").Value
    var6 = Range("AJ1").Value
'Paste Mob&Demob hours to date, to previous hours
    Cells(var1, 9).Copy
    Cells(var1, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var2, 9).Copy
    Cells(var2, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var3, 9).Copy
    Cells(var3, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var4, 9).Copy
    Cells(var4, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var5, 9).Copy
    Cells(var5, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var6, 9).Copy
    Cells(var6, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
'Create start and end cell positions for copy
    Range("AK1").Formula = "= AD1 +14"
    Range("AL1").Formula = "= AD1 +15"
    Range("AM1").Formula = "= AD1 +16"
    Range("AN1").Formula = "= AD1 +17"
    Range("AO1").Formula = "= AD1 +18"
    Range("AP1").Formula = "= AD1 +19"
    
    Dim var7 As Integer
    Dim var8 As Integer
    Dim var9 As Integer
    Dim var10 As Integer
    Dim var11 As Integer
    Dim var12 As Integer
    
    var7 = Range("AK1").Value
    var8 = Range("AL1").Value
    var9 = Range("AM1").Value
    var10 = Range("AN1").Value
    var11 = Range("AO1").Value
    var12 = Range("AP1").Value
'Paste survey hours to date, to previous hours
    Cells(var7, 9).Copy
    Cells(var7, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var8, 9).Copy
    Cells(var8, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var9, 9).Copy
    Cells(var9, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var10, 9).Copy
    Cells(var10, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var11, 9).Copy
    Cells(var11, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Cells(var12, 9).Copy
    Cells(var12, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues

and the "more tidy" but non working code is:
VBA Code:
'Find Reference cell
Range("AD1").Formula = "=MATCH(""STOP_HERE"",C:C,0)" 'This formula finds the Reference cell (gray row)
'Mob&Demob hours
    'Create start and end cell positions for copy
        Range("AE1").Formula = "=""I"" & AD1 +7"
        Range("AF1").Formula = "=""I"" & AD1 +7+(5)" 'Adjust value in brackets for total # Mob and Demob codes -1
        Range("AG1").Formula = "=""F"" & AD1 +7"
            'Copy Mob&Demob hours to date
                Range("AE1" & ":" & "AF1").Copy
                      'Paste Mob&Demob hours to date, to previous hours
                          Range("AG1").Select
                          Selection.PasteSpecial Paste:=xlPasteValues
                              'Clear sheet naming formula
                                  Range("AE1:AG1").ClearContents
'Survey hours
    'Create start and end cell positions for copy
        Range("AE1").Formula = "=""I"" & AD1 +14"
        Range("AF1").Formula = "=""I"" & AD1 +14+(5)" 'Adjust value in brackets for total # Survey codes -1
        Range("AG1").Formula = "=""F"" & AD1 +14"
            'Copy survey hours to date
                Range("AE1" & ":" & "AF1").Copy
                    'Paste survey hours to date, to previous hours
                        Range("AG1").Select
                        Selection.PasteSpecial Paste:=xlPasteValues

Any advice on how to tidy this mess up would be greatly appreciated.

Thank you in advance,

Charles
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,480
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your previous day's and new day's sheets. The new day's sheet should show your desired result. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0

Charles_P

New Member
Joined
Mar 23, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Apologies, I will attempt to do this asap. However MS Office has decided to "protect" my workbook so I am currently unable to use my macro.
 
Upvote 0

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,146
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
It looks like you want:

Code:
                Range(Range("AE1").value & ":" & Range("AF1").Value).Copy
                      'Paste Mob&Demob hours to date, to previous hours
                          Range(Range("AG1").value).PasteSpecial Paste:=xlPasteValues

and similar for the other operations.
 
Upvote 0
Solution

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,052
Office Version
  1. 2016
Its hard to code, without visual help.
Anyway, try to go with what you have wrote in original code:

VBA Code:
Option Explicit
Sub test()
Dim pos&, CopyRange As Range
pos = Evaluate("=MATCH(""STOP_HERE"",C:C,0)") ' pos = match position found
Set CopyRange = Cells(pos, "C").Offset(7, 6).Resize(13, 1) ' move down 7 rows, to right 6 columns, then resize down 13 rows = column I
CopyRange.Copy
CopyRange.Offset(0, -3).Select ' column I copy to similar range, but move to the left 3 columns = column F
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
 
Upvote 0

Charles_P

New Member
Joined
Mar 23, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
It looks like you want:

Code:
                Range(Range("AE1").value & ":" & Range("AF1").Value).Copy
                      'Paste Mob&Demob hours to date, to previous hours
                          Range(Range("AG1").value).PasteSpecial Paste:=xlPasteValues

and similar for the other operations.
That was exactly what was needed! thank you very much
 
Upvote 0

Forum statistics

Threads
1,187,138
Messages
5,961,766
Members
438,563
Latest member
Ron Gluck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top