I want my code to run on a new range after one cycle

StudExcel

New Member
Joined
Feb 27, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I am new to vba and I am trying to write a macro for transforming my data. I have been able to execute it successfully but the problem is that I want this macro to be repeated on cells that are 18 rows below. I tried to Google it but couldn't understand the concept of looping and how to enter looping in my code.

I tried this:

VBA Code:
Sub RangeVar()

Dim rng As Long
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.HasArray Then
rng = rng + 18
End If
Next cell

Range("B2").Value = rng

End Sub


but it doesn't do what I need.

Here's my macro:


Sub insertRow2()
'
' insertRow2 Macro
'
Range("A3:A19").Select
    Selection.EntireRow.Insert
    Call productSizes
   
    Range("P2:AF2").Select
    Selection.Copy
    Range("Y3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:= _
        False, Transpose:=True

Range("B2").Cut Destination:=Range("T3")
Range("A2").Cut Destination:=Range("B2")
Range("A2").Formula = "=LOWER(SUBSTITUTE(B2,"" "",""-""))"
  
    Range("A2").Copy
    Range("A3:A18").PasteSpecial xlPasteValues
   
    Range("B2").Copy Destination:=Range("B3:B18")
    Range("A3").Copy Destination:=Range("A2")
   
Call deleteimagerow
End Sub

Sub deleteimagerow()

    Range("P2:AF2").Cut Destination:=Range("AZ2")

    Range("y3:y19").Copy Destination:=Range("Y2")
   

End Sub

Sub productSizes()
Range("C2:K2").Copy
   Range("I3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:= _
        False, Transpose:=True
    Range("C2:K2").Select
    Selection.ClearContents
    Range("I3:I11").Cut Destination:=Range("I2")
    Range("H2").Value = "Size"
   

End Sub


I need to repeat it for almost 2000 times so it would be great if someone could help me out.
Thanks in advance.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
2 things.
Edit your code to have it between code tags. Highlight your code and click on the vba symbol here above. Hover your mouse pointer over it and you'll see "Quick-wrap selection as VBA code"
Explain in detail what you like to achieve.
 

StudExcel

New Member
Joined
Feb 27, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Here's the picture.
I scraped some data from a website and collected fields like name, sku, image links etc. The maximum number of images I was able to extract was 17. I need to convert this data to the shopify product template and that template requires that the image links are to be entered in column Y of the CSV file. Therefore, I need to copy and paste transpose those links into the Y column, and do it again on the row at the bottom.
1614453089407.png

2 things.
Edit your code to have it between code tags. Highlight your code and click on the vba symbol here above. Hover your mouse pointer over it and you'll see "Quick-wrap selection as VBA code"
Explain in detail what you like to achieve.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,238
Messages
5,623,549
Members
415,980
Latest member
Lidget

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