Automating row offset

itisananas

New Member
Joined
Jul 12, 2021
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey all!

SO far i tried this vba code and it works, but the cons is that it only works for 12 rows and
i want is to make like it works for a bigger amount of rows.
I am kinda new to this things and just trying to figure it out.

What it does is its reads cell value in J5 and if that value is the same as the given value then in uses the given macro.
That macro always copies the value in cell B5 and paste it in the given cell G5,G6,G7 and so on.

J5 btw is a sum of the already excisting values in the column.

So what im trying to achief is the part where i can given i variable(i think) where it can automated the row part, that it goes on like +1,+1,+1

The VBA code i am using currently:
VBA Code:
Sub Vergelijking()

    If Range("J5").Value = 1 Then
    Macro1
   
    ElseIf Range("j5").Value = 2 Then
    Macro2
   
    ElseIf Range("J5").Value = 3 Then
    Macro3
   
    ElseIf Range("J5").Value = 4 Then
    Macro4
   
    ElseIf Range("J5").Value = 5 Then
    Macro5
   
    ElseIf Range("J5").Value = 6 Then
    Macro6
   
    ElseIf Range("J5").Value = 7 Then
    Macro7
   
    ElseIf Range("J5").Value = 8 Then
    Macro8
   
    ElseIf Range("J5").Value = 9 Then
    Macro9
   
    ElseIf Range("J5").Value = 10 Then
    Macro10
   
    ElseIf Range("J5").Value = 11 Then
    Macro11
   
    ElseIf Range("J5").Value = 12 Then
    Macro12
           
    End If
   
   
End Sub

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G5").Select
    ActiveSheet.Paste
    Range("B5").Select
    Selection.ClearContents
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G6").Select
    ActiveSheet.Paste
    Range("B5").Select
    Selection.ClearContents
End Sub
Sub Macro3()
'
' Macro3 Macro
'

'
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G7").Select
    ActiveSheet.Paste
    Range("B5").Select
    Selection.ClearContents
End Sub
Sub Macro4()
'
' Macro4 Macro
'

'
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G8").Select
    ActiveSheet.Paste
    Range("B5").Select
    Selection.ClearContents
End Sub
Sub Macro5()
'
' Macro5 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G9").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
Sub Macro6()
'
' Macro6 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G10").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
Sub Macro7()
'
' Macro7 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G11").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
Sub Macro8()
'
' Macro8 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G12").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
Sub Macro9()
'
' Macro9 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G13").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
Sub Macro10()
'
' Macro10 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G14").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
Sub Macro11()
'
' Macro11 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G15").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
Sub Macro12()
'
' Macro12 Macro
'

'
    Range("B5").Select
    Selection.Copy
    Range("G16").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
 

Attachments

  • Schermafbeelding 2021-07-12 172214.png
    Schermafbeelding 2021-07-12 172214.png
    19.1 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,472
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,141,485
Messages
5,706,654
Members
421,459
Latest member
Taamrak

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