Autofill/Filldown on visible cells and using if condition

[PresidentEvil]

Hi,

I'm currently working on a vba macro that does a simple task but not able to get around the solution for a little problem that I'm facing.

I have two columns of data. Text1 is the unqiue ID that is used to fetch data from a database software (using another macro). And Text2 is the data that is fetched.

Looks something like this after data is fetched, just a few samples for example -

This data is copied to another sheet (Step2) where I do the data replacement using Autofilter. Anything that is .0000 will be changed to X and anything other than that is changed to Y in the first visible cell. and then macro uses filldown to drag the values to rest of the cells.

I recorded this macro and edited a few things on my work pc. Now the problem statement:

• If there are only X values (.00 . 000) and no Y values in the list, how do I code it? I know we can use something like 'if' condition there. But not sure how.
• Fill Down/Autofill in the code sometime won't work properly on visible cells (since they are filtered and there are cells in between, hidden).
• Offsetting to first visible cell from B1 and fill down has been difficult when there is only one cell in the list (as this does the fill down to the last row of the sheet, i.e, cell B1048576)
• Autofilter range must be dynamic, as the number of rows can change each time data is fetched.

Any help will be highly appreciated. Below is the basic code that was recorded for this demo purpose. My work PC has almost the same code as I recorded there aswell.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Step2").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=2, Criteria1:= _
        ".00  . 00000"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "X"
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=2, Criteria1:="<>X"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "Y"
    Range("B1").Select
    Selection.AutoFilter
End Sub

 

[kevin9999]

Rather than use AutoFilter, why not try something like this (change "Sheet1" to the actual sheet name)

Sub ReplaceXY()
    Dim r As Range, c As Range
    Set r = Sheets("Sheet1").Range("B2", Cells(Rows.Count, "B").End(xlUp))
    r.Replace "*.00*", "X", xlPart
    For Each c In r
        If c <> "X" Then c = "Y"
    Next
End Sub

 

[kevin9999]

Another option

Sub ReplaceXY_2()
    Dim a, i As Long
    a = Sheets("Sheet1").Range("B2", Cells(Rows.Count, "B").End(xlUp))
    For i = 1 To UBound(a)
        If a(i, 1) Like "*.00*" Then a(i, 1) = "X" Else a(i, 1) = "Y"
    Next i
    Sheets("Sheet1").Range("B2").Resize(UBound(a, 1)) = a
End Sub

 

[PresidentEvil]

Another option

Sub ReplaceXY_2()
    Dim a, i As Long
    a = Sheets("Sheet1").Range("B2", Cells(Rows.Count, "B").End(xlUp))
    For i = 1 To UBound(a)
        If a(i, 1) Like "*.00*" Then a(i, 1) = "X" Else a(i, 1) = "Y"
    Next i
    Sheets("Sheet1").Range("B2").Resize(UBound(a, 1)) = a
End Sub

Excellent! I did think about the replace but didn't know how to do this loop or 'if' along with it.

Thanks!

 

[kevin9999]

Excellent! I did think about the replace but didn't know how to do this loop or 'if' along with it.

Thanks!

Happy to help, and thanks for the feedback

 

[PresidentEvil]

Happy to help, and thanks for the feedback

This worked perfectly on the sample data but is not working on actual data. It's replacing everything to Y even though there are "0" in the B column which should've been X instead of Y.

Exact text to replace (there are spaces before the period):

.00 0 0

 

[kevin9999]

This worked perfectly on the sample data but is not working on actual data. It's replacing everything to Y even though there are "0" in the B column which should've been X instead of Y.

Exact text to replace (there are spaces before the period):

.00 0 0

Could you provide a sample of the actual data? The wildcard pattern is expecting a decimal point, "*.00*" - perhaps we can design a better match?

 

[kevin9999]

This worked perfectly on the sample data but is not working on actual data. It's replacing everything to Y even though there are "0" in the B column which should've been X instead of Y.

Exact text to replace (there are spaces before the period):

.00 0 0

Just noticed your sample data, I'll have a look at this in a while.

 

[PresidentEvil]

Could you provide a sample of the actual data? The wildcard pattern is expecting a decimal point, "*.00*" - perhaps we can design a better match?

Just noticed your sample data, I'll have a look at this in a while.

It's work data, confidential.

Thank you for the help. I think I've figured this out. I had messed up with the references a bit, however it's working now.

 

[kevin9999]

It's work data, confidential.

Thank you for the help. I think I've figured this out. I had messed up with the references a bit, however it's working now.

Glad to hear you got it working