Macro problem

P

pban92

Board Regular
Joined
Feb 26, 2010
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
I have excel files each with two columns (Column-A and Column-B) with variable number of rows. Holding x as the number of rows, where x varies for diff files, I would like to create a marco which will do the following,

1. A(x+1) = A(x)+1
2. C1 = A2-A1
3. D1 = B1/1048576/C1
4. E1 = D1*C1

The following scripts are supposed to do #1. But I am not sure what the syntax mean. Any explanation is welcome. Thank you.

Range("A1").Select
Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
 

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.
Sort by date Sort by votes
Sorry the above scripts are not at all meant for #1. How can I do that?
 
Upvote 0
1. To add 1 to the last row (where number of rows unknown) of first column,

HTML: 
Sub Add1plusPadding()

Dim CA, CC, CE, CG, CI, CK, CM, CO, CQ, CS, CU, CW As Long

CA = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(CA, "A") = Cells(CA - 1, "A") + 1

CC = Range("C" & Rows.Count).End(xlUp).Row + 1
Cells(CC, "C") = Cells(CC - 1, "C") + 1

CE = Range("E" & Rows.Count).End(xlUp).Row + 1
Cells(CE, "E") = Cells(CE - 1, "E") + 1

CG = Range("G" & Rows.Count).End(xlUp).Row + 1
Cells(CG, "G") = Cells(CG - 1, "G") + 1

CI = Range("I" & Rows.Count).End(xlUp).Row + 1
Cells(CI, "I") = Cells(CI - 1, "I") + 1

CK = Range("K" & Rows.Count).End(xlUp).Row + 1
Cells(CK, "K") = Cells(CK - 1, "K") + 1

CM = Range("M" & Rows.Count).End(xlUp).Row + 1
Cells(CM, "M") = Cells(CM - 1, "M") + 1

CO = Range("O" & Rows.Count).End(xlUp).Row + 1
Cells(CO, "O") = Cells(CO - 1, "O") + 1

CQ = Range("Q" & Rows.Count).End(xlUp).Row + 1
Cells(CQ, "Q") = Cells(CQ - 1, "Q") + 1

CS = Range("S" & Rows.Count).End(xlUp).Row + 1
Cells(CS, "S") = Cells(CS - 1, "S") + 1

CU = Range("U" & Rows.Count).End(xlUp).Row + 1
Cells(CU, "U") = Cells(CU - 1, "U") + 1

CW = Range("W" & Rows.Count).End(xlUp).Row + 1
Cells(CW, "W") = Cells(CW - 1, "W") + 1
      
End Sub

2. To insert column,

HTML: 
Sub Add1plusPadding()

Dim CA, CC, CE, CG, CI, CK, CM, CO, CQ, CS, CU, CW As Long

CA = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(CA, "A") = Cells(CA - 1, "A") + 1

CC = Range("C" & Rows.Count).End(xlUp).Row + 1
Cells(CC, "C") = Cells(CC - 1, "C") + 1

CE = Range("E" & Rows.Count).End(xlUp).Row + 1
Cells(CE, "E") = Cells(CE - 1, "E") + 1

CG = Range("G" & Rows.Count).End(xlUp).Row + 1
Cells(CG, "G") = Cells(CG - 1, "G") + 1

CI = Range("I" & Rows.Count).End(xlUp).Row + 1
Cells(CI, "I") = Cells(CI - 1, "I") + 1

CK = Range("K" & Rows.Count).End(xlUp).Row + 1
Cells(CK, "K") = Cells(CK - 1, "K") + 1

CM = Range("M" & Rows.Count).End(xlUp).Row + 1
Cells(CM, "M") = Cells(CM - 1, "M") + 1

CO = Range("O" & Rows.Count).End(xlUp).Row + 1
Cells(CO, "O") = Cells(CO - 1, "O") + 1

CQ = Range("Q" & Rows.Count).End(xlUp).Row + 1
Cells(CQ, "Q") = Cells(CQ - 1, "Q") + 1

CS = Range("S" & Rows.Count).End(xlUp).Row + 1
Cells(CS, "S") = Cells(CS - 1, "S") + 1

CU = Range("U" & Rows.Count).End(xlUp).Row + 1
Cells(CU, "U") = Cells(CU - 1, "U") + 1

CW = Range("W" & Rows.Count).End(xlUp).Row + 1
Cells(CW, "W") = Cells(CW - 1, "W") + 1
      
End Sub
3. The calculation,
HTML: 
Sub PdfData()
'
' Macro2 Macro
' Macro recorded 16/05/2011 by srabon


'01£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("C1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("D1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("E1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     


'02£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("H1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("I1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("J1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'03£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("M1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("N1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("O1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'04£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("R1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("S1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("T1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'05£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("W1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("X1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("Y1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'06£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("AB1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AC1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AD1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'07£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("AG1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AH1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AI1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'08£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("AL1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AM1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AN1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'09£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("AQ1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AR1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AS1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'10£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("AV1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AW1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("AX1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'11£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("BA1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("BB1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("BC1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     

'12£££££££££££££££££££££££££££££££££££££££££££££££££££££££


    Range("BF1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("BG1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]/1048576/RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
      Range("BH1").Select
      Do Until IsEmpty(ActiveCell.Offset(, -1))
      ' Continue until cell in 1 column left is not empty
      ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
      Loop
     
     


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Employee Attendance Tracker Template.
Replies
3
Views
212
6StringJazzer
6StringJazzer
A
  • Solved
Extract and Merge Files
Replies
1
Views
133
rowebca
rowebca
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
461
Sean15
Sean15
S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
256
Fluff
Fluff
S
  • Solved
VBA autofilling code
Replies
6
Views
574
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,586
Messages
6,179,722
Members
452,939
Latest member
WCrawford

Share this page

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
Back
Top