Overlapping formulas - VBA

[guapo]

Hi, I'm having a problem with this code. I have the formulas right and the filters aswell, the problem is although I select only visible cells, the formulas are overlapping each other and I'm losing the ones that I wrote before.

Is there any way I can concatenate formulas or just select the cells that are not filled without losing previous data??

Regards,

guapo

CODE:

Sub magda()


ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=31, Criteria1:=Array( _
"N2", "N3", "N4", "N5", "N6"), Operator:=xlFilterValues

ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=46, Criteria1:= _
"FALSE"

Range("AG2:AG5000").FormulaR1C1 = _
"=IF(RC[-2]=""N2"",""Tratamento indevido de N2"",IF(RC[-2]=""N3"",""Tratamento indevido de N3"",IF(RC[-2]=""N4"",""Tratamento indevido de N4"",IF(RC[-2]=""N5"",""Tratamento indevido de N5"",IF(RC[-2]=""N6"",""Tratamento indevido de N6"","""")))))"

ActiveSheet.ShowAllData

ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=24, Criteria1:= _
"RETENÇÃO SOHO"

Range("AG2:AG5000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = _
"=IF(RC[-10]=""Indefinido"",""Abandono SR"",IF(ISNUMBER(SEARCH(""LOJA"",RC[-10])),""Motivo incorreto"",""Tratamento indevido""))"

ActiveSheet.ShowAllData

ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=47, Criteria1:="FALSE"

Range("AG2:AG5000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = _
"=IF(RC[-10]=""Indefinido"",""Abandono SR"",""Motivo incorreto"")"

 

[RCBricker]

Hi, I'm having a problem with this code. I have the formulas right and the filters aswell, the problem is although I select only visible cells, the formulas are overlapping each other and I'm losing the ones that I wrote before.

Is there any way I can concatenate formulas or just select the cells that are not filled without losing previous data??

Regards,

guapo

CODE:

Sub magda()


ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=31, Criteria1:=Array( _
"N2", "N3", "N4", "N5", "N6"), Operator:=xlFilterValues

ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=46, Criteria1:= _
"FALSE"

Range("AG2:AG5000").FormulaR1C1 = _
"=IF(RC[-2]=""N2"",""Tratamento indevido de N2"",IF(RC[-2]=""N3"",""Tratamento indevido de N3"",IF(RC[-2]=""N4"",""Tratamento indevido de N4"",IF(RC[-2]=""N5"",""Tratamento indevido de N5"",IF(RC[-2]=""N6"",""Tratamento indevido de N6"","""")))))"

ActiveSheet.ShowAllData

ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=24, Criteria1:= _
"RETENÇÃO SOHO"

Range("AG2:AG5000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = _
"=IF(RC[-10]=""Indefinido"",""Abandono SR"",IF(ISNUMBER(SEARCH(""LOJA"",RC[-10])),""Motivo incorreto"",""Tratamento indevido""))"

ActiveSheet.ShowAllData

ActiveSheet.Range("$A$1:$BK$5000").AutoFilter Field:=47, Criteria1:="FALSE"

Range("AG2:AG5000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = _
"=IF(RC[-10]=""Indefinido"",""Abandono SR"",""Motivo incorreto"")"

everywhere you are inserting formulas add

.specialcells(xlcelltypevisible)

otherwise based on your code you are entering each formula into the entire range of AG2:AG5000.

rich

 

[guapo]

everywhere you are inserting formulas add

.specialcells(xlcelltypevisible)

otherwise based on your code you are entering each formula into the entire range of AG2:AG5000.

rich

First of all thank you for your quick response.

But I'm still having doubts... Isn't that what I have in my code?

Range("AG2:AG5000").SpecialCells(xlCellTypeVisible).Formula: = "......."

Or do I have to place SpecialCells command somewhere else?

Sorry if stupid question, I am a beginner :D

guapo

 

[Jonmo1]

Your filters are based on 3 different columns.
Any row that meets multiple critiera will have the last formula written for a true criteria.
Example

Say Row 7 for example.
Looking at the first filter (column AT)
If say AT7 is FALSE (this meets the first filter's criteria)
Then AG7 recieves the first formula.

Then move on to the 2nd filter (on column X).
Still looking at row 7.
If X7 = RETENÇÃO SOHO (this meets the 2nd criteria)
Then AG7 recievs the 2nd formula (overwriting the first)

And the same for the 3rd criteria.


Hope that helps.

 

[guapo]

Your filters are based on 3 different columns.
Any row that meets multiple critiera will have the last formula written for a true criteria.
Example

Say Row 7 for example.
Looking at the first filter (column AT)
If say AT7 is FALSE (this meets the first filter's criteria)
Then AG7 recieves the first formula.

Then move on to the 2nd filter (on column X).
Still looking at row 7.
If X7 = RETENÇÃO SOHO (this meets the 2nd criteria)
Then AG7 recievs the 2nd formula (overwriting the first)

And the same for the 3rd criteria.


Hope that helps.

Hi, thank you for your answer.

That's exactly what is happening! I wonder if there is a way to ignore already filled cells or just prioritize the formulas.

guapo

 

[Jonmo1]

You could prioritize the formulas, Lowest priority first, Higher priority goes last

OR include previous criteria (reversed) in the subsequent filters.

Filter1, Feild1 = "x"
enter formula
Filter2, Field1 "<>x" Field2 ="Hello"
enter formula
Filter3, Field1 "<>x" Field2 "<>Hello" Field3 = "whatever"
enter formula