# Autofill/Filldown on visible cells and using if condition



## PresidentEvil (Dec 15, 2022)

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 (Dec 16, 2022)

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 (Dec 16, 2022)

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 (Dec 16, 2022)

kevin9999 said:


> Another option
> 
> ```
> Sub ReplaceXY_2()
> ...


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

Thanks!


----------



## kevin9999 (Dec 16, 2022)

PresidentEvil said:


> 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 (Dec 16, 2022)

kevin9999 said:


> 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 (Dec 16, 2022)

PresidentEvil said:


> 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 (Dec 16, 2022)

PresidentEvil said:


> 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 (Dec 16, 2022)

kevin9999 said:


> 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 said:


> 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 (Dec 16, 2022)

PresidentEvil said:


> 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 🙂


----------

