Paste Absolute value with VBA

[GPUSA810]

I am new to VBA, but I have been able to edit my recording enough to get what I want to this point. Here is what I have so far:

 ActiveCell.Offset(-12, 0).Select    Range(ActiveCell, ActiveCell.Offset(0, 16)).Select
    Selection.Copy
    ActiveCell.Offset(12, 0).Select
    ActiveSheet.Paste
    ActiveCell.Select
    Application.CutCopyMode = False
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/[COLOR=#ff0000]R[8]C[3][/COLOR]*RC[-1])"
    ActiveCell.Select
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(7, 0)), Type:=xlFillDefault
    Range(ActiveCell, ActiveCell.Offset(7, 0)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

The above red relative cell entry results in the correct cell for the edited formula, but for the AutoFill to work properly in the remaining code I need the resulting cell reference to be absolute:

Line: ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R[8]C[3]*RC[-1])"
Result: "K580" = IF(H580=0,"-",(E580/N576)*J580)
Wanted Result: "K580" = IF(H580=0,"-",(E580/$N$576)*J580)

Thanks

 

[Joe4]

Welcome to the Board!

The square brackets indicate relative to the cell you are pasting.
To get absolute reference, use the numerical index of the row/column, i.e.
to reference N576, instead of using:

R[8]C[3]

use

R576C14

Note: You can also get VBA to figure this out for you.
Just turn on the Macro Recorder and enter =$N$576 into any cell, and take a look at the VBA code that Excel came back with.
I find when I need to get the syntax of a formula in R1C1 format, I simply turn on the Macro Recorder, enter the formula into any cell, and then steal the formula that was recorded.

 

[Fluff]

Another option (untested) is

ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R[8]C[3]*RC[-1])"
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlR1C1, xlA1, xlAbsolute)

 

[GPUSA810]

Thanks for the quick replies.
Joe: If I use

r576c14

When I select the next range of cells to run the macro
Result: "K592" = IF(H592=0,"-",(E592/$N$576)*J592)
Wanted Result: "K592" = IF(H592=0,"-",(E592/$N$588)*J592)

Fluf: If you have time to walk me through what should have happened. I would love to continue learning, but:

[COLOR=#333333]ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R[8]C[3]*RC[-1])"[/COLOR]
[COLOR=#333333]ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlR1C1, xlA1, xlAbsolute)[/COLOR]

Returns a Run-time error '1004':
Application-defined or Object-defined error

 

[Fluff]

There's an error in your formula. You have 2 ( but only 1 ), hence the error.
That said, the line I added should be

ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute)

 

[Joe4]

So this is within some sort of loop, right? Then you can count the number of loops to add 12 each time.
So if "i" was your counter, then the formula would look like:

    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C[-3]=0,""-"",(R[-2]C[-6]/R" [COLOR=#ff0000]& 576 + (i * 12) &[/COLOR] "C14)*R[-2]C[-1])"

Note your loop is pretty inefficient. You do not need to use ActiveCell and Select statements. That actually slows the loop down.
If you post your whole loop, we can probably help you write it much more efficiently.

 

[Joe4]

There's an error in your formula. You have 2 ( but only 1 ), hence the error.
That said, the line I added should beCode:
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute)

Fluff,

I don't think that is going to work for them. That appears to makes ALL range references in the formula absolute. They only want the one range reference in the formula to be absolute.

 

[Fluff]

@Joe4
Agreed, I saw that the Op wanted the cell reference absolute & never read further.

 

[GPUSA810]

So this is within some sort of loop, right? Then you can count the number of loops to add 12 each time.
So if "i" was your counter, then the formula would look like:

    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C[-3]=0,""-"",(R[-2]C[-6]/R" [COLOR=#ff0000]& 576 + (i * 12) &[/COLOR] "C14)*R[-2]C[-1])"

Note your loop is pretty inefficient. You do not need to use ActiveCell and Select statements. That actually slows the loop down.
If you post your whole loop, we can probably help you write it much more efficiently.

Sorry this is where I wish I could actually read the "VBA for Dummies" book I bought last Friday fast enough that I do this on my own. I hate thinking I am wasting your time. Thank you thank you for the help.

I don't think it is a loop, but I think if I knew what I was doing I could code it to be a loop so that it AutoFilled say 30 days at a time. I am trying to AutoFill a Data Base style entry page for my Production team to enter their Production every day. I am pretty sure I understand your code above and you are understanding what I want to do. But I am just trying to AutoFill one day at time(12 row range of cells)right now and I will read and learn how to do more than that on my own.

Yes each range is 12 rows of that days production. Because I have that absolute cell reference within the 12 rows I can't Autofill the next range without manually entering the next absolute cell reference which is 12 rows below the last one. So the relative reference retruns the cell that I need but then I need the cell reference to then be absolute to AutoFill the formula down the range of 8 cell below the cell that the formula is modified.

I have posted the complete code of what I am doing.
I copied the wrong cell in my original post it should have read:
Line: ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R[8]C[3]*RC[-1])"
Result: "K568" = IF(H568=0,"-",(E568/N576)*J568)
Wanted Result: "K568" = IF(H568=0,"-",(E568/$N$576)*J568).

This is what the original macro recorded:

Range("K556:AA556").Select    Selection.Copy
    Range("K568").Select
    ActiveSheet.Paste
    Range("K568").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R576C14)*RC[-1])"
    Range("K568").Select
    Selection.AutoFill Destination:=Range("K568:K575"), Type:=xlFillDefault
    Range("K568:K575").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

This is what the next macro recording looks like if I go to the next range of cell and record again the AutoFill that I have 48 times so far manually, and want to do it with a macro:

Range("K568:AA568").Select    Selection.Copy
    Range("K580").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=6
    Range("K580").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R588C14)*RC[-1])"
    Range("K580").Select
    Selection.AutoFill Destination:=Range("K580:K587"), Type:=xlFillDefault
    Range("K580:K587").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

I lost a post that I was reading that suggested putting a msg box in the code to manually enter that cell reference every time which would be faster than selecting the cell, selecting the area of the formula for editing, editing the cell reference, and AutoFill.

I hope I make sense, Thanks again

 

[Joe4]

Better yet, is there any way we can dynamically figure out where the next 12 rows should start?
For example, can we look at some column, see where the last data entered is, and use that to determine where to start the next batch?
If you can tell us that logic, we should be able to program for it!

Maybe we can use column K? Where does the next batch start, relative to where the previous section ends?