Insert into visible cells only if there are visible cells

[jordanburch]

Hey Guys,

I have the following
Columns("J:J").Select
Range("aj2:aj6000").SpecialCells(xlCellTypeVisible).Formula = "COMPLETE"

Range("AM71").Select
Range("al2:al6000").SpecialCells(xlCellTypeVisible).Formula = "OFF SET"

Range("AL78").Select

it works great but only if there are cells that are selected when filtered
Columns("Au:Au").Select
Selection.Style = "Currency"
ActiveSheet.Range("$A$1:$AY$6000").AutoFilter Field:=47, Criteria1:= _
"=$(0.00)", Operator:=xlOr, Criteria2:="=$-"
sometimes there wont be any results though. It then errors out saying that there arent any cells to be inserted into because there wasnt anything in the selection. How can I code around this?

Jordan

 

[Fluff]

How about

Sub jordanburch()
   Dim UsdRws As Long
   
   With ActiveSheet
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1:AY" & UsdRws).AutoFilter 47, "=$(0.00)", xlOr, "=$-"
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("J").Value = "COMPLETE"
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("AI").Value = "OFF SET"
      End If
   End With
End Sub

 

[jordanburch]

thanks fluff! works great!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jordanburch]

You're welcome & thanks for the feedback.

Hey Fluff! Trying to resurect this thread. I am getting the error

Dim UsdRws As Long

With ActiveSheet
UsdRws = .Range("A" & Rows.Count).End(xlUp).Row

If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
.AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("AJ").Value = "COMPLETE"
.AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("AL").Value = "ODS ATG-D"
End If
End With


runtime error 6 overflow

If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
thats the error line.

Im not sure whats going on. It worked before. This time there is nothing to select and all visible cells are empty so it should skip over the code. Any help is appreciated!

jORDAN

 

[Fluff]

Sounds like the data was already filtered when you ran the code.

 

[jordanburch]

Sounds like the data was already filtered when you ran the code.

yes that is correct. This is the code directly above. Isnt that what the purpose is?

Range("av2:av6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'ODS ATG-D'!C[155],0)"
Range("a1:ax6000").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$av$6000").AutoFilter Field:=48, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues


basically if in field 48 there are matches then I want it to insert complete and ODS ATG D parts, if there arent any matches I want it to exit the sub. where should I insert the filer?

Jordan

 

[Fluff]

You made no mention of running the code on previously filtered data.
Try removing the filter before you run the code.

 

[jordanburch]

You made no mention of running the code on previously filtered data.
Try removing the filter before you run the code.

it runs fine without filter. Basically i want it to insert those values after it is filtered if there are matches and if its blank just to end the sub.

 

[Fluff]

You need to apply the filters after you calculate UsdRws, otherwise you can get problems.