Offset referencing only working for formulas, not copying? [VBA]

[nidenikolev]

I have two 'with' statements that select first visible cell and it's not selecting visible cells in column in order to copy+pastevalue.

I just want it to: select first visible cell in column AR=>select down to last visible cell in AR=>copy=>pastevalues

I used this exact structure for RC[-1] formula implementation,but instead of putting in ...(xlCellTypeVisible).R1C1Formula I need to add something like .Copy

Why isn't this copying, isn't it the same logic?

Can anyone help?

Code:

With ActiveSheet.Range("AR2").CurrentRegion
.AutoFilter Field:=5, Criteria1:="<>"
End With

With .Columns(44)
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy
    .PasteSpecial xlPasteValues
End With

 

[Fluff]

Do you have

On Error Resume Next

Anywhere in your code?
Because this line

.Offset(1).SpecialCells(xlCellTypeVisible).Copy

will give you an error, which you have made no mention of.
As you are operating on the Entire Column, you cannot offset down 1 cell

 

[nidenikolev]

Why am I getting an Application-defined error

I get an application error when I execute this block of code:

line that errors: .Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[1]+RC[3]+RC[6]"

With ActiveSheet.Range("AR2").CurrentRegion
.AutoFilter Field:=5, Criteria1:=""
End With
With .Columns(44)
.Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[1]+RC[3]+RC[6]"
End With

What I am aiming to do is filter down to blank values in Column F and then input the R1C1 formula in the first visible cell in Column AR and have that populate throughout the rest of the columns visible cells. This worked just fine when I did almost the same thing in another column, but now it wants to error out. How can I fix this?

 

[Joe4]

Re: Why am I getting an Application-defined error

I think the issue is that you are trying to apply it to the entire column 44. That means EVERY row on your sheet in column 44.
Then, you are trying to Offset (move down) one row. However, since one cell of your range is the very last possible row in column 44, you cannot move down one row from that (that would move off the sheet). So I think you may need to restrict the range you are trying to apply this to. Maybe find the last row with data in column 44, and only use that range (instead of the whole column).

Also, unless this is embedded in another "With" range statement, I think you need to remove the period in front of Columns, i.e.

With Columns(44)

 

[Joe4]

Re: Why am I getting an Application-defined error

As this is almost a duplicate of https://www.mrexcel.com/forum/excel-...ml#post5083625

I didn't see that before I replied, so I merged the two threads together so all the responses are in the same place.
The thread is unlocked and can be responded to.

Please heed Fluff's advice, and do not post the same question multiple times. He had actually replied to your question, and you did not follow-up to your response.

 

[nidenikolev]

Re: Why am I getting an Application-defined error

sorry, his suggestion didn't correct anything. I'm confused as to why this worked how I wanted to for inputting a formula in one column over, but now it's error-ing out?

 

[Joe4]

Re: Why am I getting an Application-defined error

sorry, his suggestion didn't correct anything

OK, then you should just respond to the original thread, mentioning that along with all pertinent details so that he knows and can look into it further.

Typically, if someone suggests an answers that doesn't work for the person, it is often because the original question left off some important details.
So if you do not get the answer you need, you should try to provide more details. Specific examples are often very helpful, so we can see the structure and data you are working with.

While you cannot upload files to this site, there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[nidenikolev]

unfortunately this did not correct the error.

I decided to just copy and paste the whole column as values and then input the formulas for data I need to house formulas by autofiltering to it.
I have a column :
example:
COLUMN AR
row 1 - 000000
row 76 - 999999
row 157 - 999999
everywhere in between those row numbers are hidden cells, how do I just select those three cells =>input a formula "=RC[1]+RC[3]+RC[6]" and have that apply to only the visible cells (rows 1,76,157)? I keep getting an application-defined or object-defined error, even though I used this with/end with statement identically before in another block of code and it didn't error out.

 

[Fluff]

How about

Columns(44).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[1]+RC[3]+RC[6]"

 

[Joe4]

We both told you what was causing the error (being caused by your Offset):

As you are operating on the Entire Column, you cannot offset down 1 cell

Then, you are trying to Offset (move down) one row. However, since one cell of your range is the very last possible row in column 44, you cannot move down one row from that (that would move off the sheet). So I think you may need to restrict the range you are trying to apply this to. Maybe find the last row with data in column 44, and only use that range (instead of the whole column).

Do you really want that Offset? If not, Fluff's solution above should work.

Otherwise, if you really do want it, then take my suggestion and do not apply it to the whole column, but rather just down to the last populated row in your column, i.e.

    Dim lr As Long
'   Find last row with data in column 44
    lr = Cells(Rows.Count, 44).End(xlUp).Row
    With Range(Cells(2, 44), Cells(lr, 44))
        .Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[1]+RC[3]+RC[6]"
    End With