Running Macros In diffirenet sheet.

[Fsailoh]

Hi, I am running a MACRO in sheet2 by clicking on a bottom on sheet1, all of my code is working very well, except here:

.Columns("H2:I20000").SpecialCells(xlCellTypeBlanks).Select
.Selection.FormulaR1C1 = "=R[-1]C"

What i am trying to do here is to tell excel to go to Find & Select > Go To Special > Select Blanks, and then apply certain for all of the Blanks Cells which is "=R[-1]C".

thanks.

 

[footoo]

.Range("H2:I20000").SpecialCells(xlCellTypeBlanks).Select

 

[Fsailoh]

same problem, now it tells me:

"compile error:
Method or data member not found"

and it highlight ".Selection".

 

[footoo]

Post the rest of your code.

 

[Fsailoh]

Sub Test()

Dim ws As Worksheet
Set ws = Sheets("Sheet2")
With ws
    .Columns("A:E").Copy
    .Range("H1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
               
    .Range("$H$1:$L$1048354").AutoFilter Field:=1, Criteria1:="="
    .Range("H2:H20000").ClearContents
    .Range("$H$1:$L$1048354").AutoFilter Field:=1
           
    .Range("$H$1:$L$1048354").AutoFilter Field:=2, Criteria1:="="
    .Range("I2:I20000").ClearContents
    .Range("$H$1:$L$1048354").AutoFilter Field:=2
             
    .Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
    .Range("J2:J20000").ClearContents
    .Range("$H$1:$L$1048354").AutoFilter Field:=3
    
   
   .Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
    .Range("K2:L20000").ClearContents
    .Range("$H$1:$L$1048354").AutoFilter Field:=3
    
    .Range("H1:I1").ClearContents
    .Range("H1").FormulaR1C1 = " "
    .Range("I1").FormulaR1C1 = " "
     
  [COLOR=#FF0000]   .Columns("H2:I20000").SpecialCells(xlCellTypeBlanks).Select
    .Selection.FormulaR1C1 = "=R[-1]C"
    [/COLOR]
    .Columns("H:L").Copy
    .Columns("H:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    .Range("H1").FormulaR1C1 = "Group"
    .Range("I1").FormulaR1C1 = "GL"
    

    End With
End Sub

 

[Fluff]

When posting code please use code tags, the # icon in the reply window.

Try

On Error Resume Next
.Range("H2:I20000").SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
On Error GoTo 0

 

[Fsailoh]

Fluf, where exactly i should paste this code?

 

[Fluff]

In place of the two lines in red from post#5

 

[Fsailoh]

YESSSSSSSSSSSSSS now it works very well! thanks Fluff, could you give me an idea about what does this code mean?

 

[Fluff]

You're welcome & thanks for the feedback.
The code simply puts the formula in all blank cells in the your range