Overlapping formulas - VBA

guapo

New Member
Joined
Feb 1, 2016
Messages
4
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

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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

New Member
Joined
Feb 1, 2016
Messages
4
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 
Last edited:

guapo

New Member
Joined
Feb 1, 2016
Messages
4
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Forum statistics

Threads
1,081,680
Messages
5,360,512
Members
400,589
Latest member
Mikealphatangoc

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top