vba, macro, shift down after autofilter

[mawar5530]

Hi Friends,

I am new to vba, really need help to find solution.
After autofilter the selction of "Poitem" example, i want to change G coloumn number beloging to "Poitem" to "zero" value
i dont find any way to offset, because the range keep on changing acording to report, so the offset not working for me.

 

[mawar5530]

Why we specify G2, instead ? because if i filter like different field example"PurReq", then the formula not working right ?

 

[mawar5530]

Why we specify G2, instead ? because if i filter like different field example"PurReq", then the formula not working right ?

'Range("H2:H" & Range("H" & Rows.Count).End(xlUp).Row).SpecialCells(12).Formula = "=IF(F2=0, ABS(F2-G2),F2-G2)"

 

[MARK858]

There is nothing wrong with the syntax you have posted, I can't tell what you mean by "PurReq" as I can't see by your image what headings go with what columns (I can't actually see a column called "PurReq" even).

If you need anymore help post your code in the thread (in code tags) and post a sample of your unfiltered data (about 20 rows) using the boards XL2BB addin, then explain what issue you have.

 

[mawar5530]

Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("A:AT").AutoFilter Field:=4, Criteria1:=Array( _
"POitem", "PurRqs", "ShpgNt"), Operator:=xlFilterValues
Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row).SpecialCells(12).Value = 0
Range("H3:H" & Range("H" & Rows.Count).End(xlUp).Row).SpecialCells(12).Formula = "=IF(F2=0, ABS(F2-G2),F2-G2)"
Selection.Copy
Selection.AutoFilter

 

[mawar5530]

Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("A:AT").AutoFilter Field:=4, Criteria1:=Array( _
"POitem", "PurRqs", "ShpgNt"), Operator:=xlFilterValues
Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row).SpecialCells(12).Value = 0
Range("H3:H" & Range("H" & Rows.Count).End(xlUp).Row).SpecialCells(12).Formula = "=IF(F2=0, ABS(F2-G2),F2-G2)"
Selection.Copy
Selection.AutoFilter

I try to acess to xl2bb, but something wrong in setup.
sucessfully install but i cant mr excel in ribon and also the addin not found but selected.

 

[MARK858]

First of all you need to use vba or Rich code tags when posting code, you have quoted in the previous post. Anyway...
You haven't got any data in column H so it can't find a last row using that column. Try...

Range("H2:H" & Range("G" & Rows.Count).End(xlUp).Row).SpecialCells(12).Formula = "=IF(F2=0, ABS(F2-G2),F2-G2)"

 

[mawar5530]

First of all you need to use vba or Rich code tags when posting code, you have quoted in the previous post. Anyway...
You haven't got any data in column H so it can't find a last row using that column. Try...

Range("H2:H" & Range("H" & Rows.Count).End(xlUp).Row).SpecialCells(12).Formula = "=IF(F2=0, ABS(F2-G2),F2-G2)"

not working.
My problem is, after filter the range will run according to data list.
Therefore, cannot specify the range of.
Once filter, the formula need to paste according at column "H"

 

[MARK858]

That code that you posted is not what I posted in my last post, which was

Range("H2:H" & Range("G" & Rows.Count).End(xlUp).Row).SpecialCells(12).Formula = "=IF(F2=0, ABS(F2-G2),F2-G2)"

Test it without altering it please.

 

[mawar5530]

That code that you posted is not what I posted in my last post, which was

Range("H2:H" & Range("G" & Rows.Count).End(xlUp).Row).SpecialCells(12).Formula = "=IF(F2=0, ABS(F2-G2),F2-G2)"

Test it without altering it please.

Superb!
Thank you, tq, tq....
Its work and very sorry.

 

[MARK858]

You're welcome