insert formuls into visible cells only

jordanburch

Board Regular
Joined
Jun 10, 2016
Messages
213
Office Version
  1. 2016
Sub CRISFILTERFINAL3()
'
' CRISFILTERFINAL3 Macro
'

' Sheets.Add.Name = "CRIS"
Sheets.Add.Name = "CRIS"
Dim Xrow As Long, WS As Worksheet, ws2 As Worksheet, dng As Range, dng2 As Range, dng3 As Range, dng4 As Range, dng5 As Range, dng6 As Range, dng7 As Range, dng8 As Range, dng9 As Range, dng10 As Range, dng11 As Range, dng12 As Range, dng13 As Range, dng14 As Range, dng15 As Range, dng16 As Range, dng17 As Range, dng18 As Range, dng19 As Range, dng20 As Range, dng21 As Range, dng22 As Range

Xrow = Cells(Rows.Count, "K").End(xlUp).Row


Set WS = ThisWorkbook.Worksheets("CRIS")
Set ws2 = ThisWorkbook.Worksheets("MAY FY20 IDARRS")
Set dng = WS.Range("af2:af" & Xrow)
Set dng2 = WS.Range("ag2:ag" & Xrow)
Set dng3 = WS.Range("ah2:ah" & Xrow)
Set dng4 = WS.Range("ai2:ai" & Xrow)
Set dng5 = WS.Range("aj2:aj" & Xrow)
Set dng6 = WS.Range("ak2:ak" & Xrow)
Set dng7 = WS.Range("al2:al" & Xrow)
Set dng8 = ws2.Range("ao2:ao" & Xrow)
Set dng9 = ws2.Range("ap2:ap" & Xrow)
Set dng10 = ws2.Range("aq1:aq" & Xrow)
Set dng11 = ws2.Range("ar1:ar" & Xrow)
Set dng12 = ws2.Range("as2:as" & Xrow)
Set dng13 = ws2.Range("at2:at" & Xrow)
Set dng14 = ws2.Range("au2:au" & Xrow)
Set dng15 = ws2.Range("av2:av" & Xrow)
Set dng16 = ws2.Range("aw2:aw" & Xrow)
Set dng17 = ws2.Range("A2:A" & Xrow)
Set dng18 = ws2.Range("B2:B" & Xrow)
Set dng19 = ws2.Range("C2:C" & Xrow)
Set dng20 = ws2.Range("d2:D" & Xrow)
Set dng21 = ws2.Range("E2:E" & Xrow)
Set dng22 = ws2.Range("F2:F" & Xrow)




Sheets("3875 Indy").Select
Cells.Select
Selection.Copy
Sheets("CRIS").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:F").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Recon Period"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Source2"
Range("D1").Select
ActiveCell.FormulaR1C1 = "ALC"
Range("E1").Select
ActiveCell.FormulaR1C1 = "FDRI"
Range("F1").Select
ActiveCell.FormulaR1C1 = "DPT"
Cells.Select
Cells.EntireColumn.AutoFit

Range("AF1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "subkey"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = ""
Range("AI1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "Master"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "match"
'Set dng = WS.Range("af2:af" & Xrow)Set dng2 = WS.Range("ag2:ag" & Xrow) Set dng3 = WS.Range("ah2:ah" & Xrow) Set dng4 = WS.Range("ai2:ai" & Xrow)
'Set dng5 = WS.Range("aj2:aj" & Xrow)Set dng6 = WS.Range("ak2:ak" & Xrow) Set dng7 = WS.Range("al2:al" & Xrow)Set dng8 = ws2.Range("ao2:ao" & Xrow)
'Set dng9 = ws2.Range("ap2:ap" & Xrow)Set dng10 = ws2.Range("aq1:aq" & Xrow)Set dng11 = ws2.Range("ar1:ar" & Xrow)Set dng12 = ws2.Range("as2:as" & Xrow)
'Set dng13 = ws2.Range("at2:at" & Xrow)Set dng14 = ws2.Range("au2:au" & Xrow)Set dng15 = ws2.Range("av2:av" & Xrow)Set dng16 = ws2.Range("aw2:aw" & Xrow)
'Set dng17 = ws2.Range("A2:A" & Xrow)Set dng18 = ws2.Range("B2:B" & Xrow)Set dng19 = ws2.Range("C2:C" & Xrow)Set dng20 = ws2.Range("d2:D" & Xrow)
'Set dng21 = ws2.Range("E2:E" & Xrow)Set dng22 = ws2.Range("F2:F" & Xrow)
dng.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+IF(RC[-20]="""",""0000"","""")"
dng2.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-22],RC[-1],RC[-21])"
dng3.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+SUMIF(C[-1],RC[-1],C[-8])"
dng5.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-3],RC[-2])"
Sheets("MAY FY20 IDARRS").Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=39, Criteria1:= _
"=Not on CO SAR", Operator:=xlOr, Criteria2:="="

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=34, Criteria1:= _
"2 - ALC 5700"

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=36

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=43, Criteria1:= _
"3875.001"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AT1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AV1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AW1").Select
ActiveCell.FormulaR1C1 = "master key"
'Set dng5 = WS.Range("aj2:aj" & Xrow)Set dng6 = WS.Range("ak2:ak" & Xrow) Set dng7 = WS.Range("al2:al" & Xrow)Set dng8 = ws2.Range("ao2:ao" & Xrow)
'Set dng9 = ws2.Range("ap2:ap" & Xrow)Set dng10 = ws2.Range("aq1:aq" & Xrow)Set dng11 = ws2.Range("ar1:ar" & Xrow)Set dng12 = ws2.Range("as2:as" & Xrow)
'Set dng13 = ws2.Range("at2:at" & Xrow)Set dng14 = ws2.Range("au2:au" & Xrow)Set dng15 = ws2.Range("av2:av" & Xrow)Set dng16 = ws2.Range("aw2:aw" & Xrow)
'Set dng17 = ws2.Range("A2:A" & Xrow)Set dng18 = ws2.Range("B2:B" & Xrow)Set dng19 = ws2.Range("C2:C" & Xrow)Set dng20 = ws2.Range("d2:D" & Xrow)
'Set dng21 = ws2.Range("E2:E" & Xrow)Set dng22 = ws2.Range("F2:F" & Xrow)
dng12.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-40],RC[-39])"
dng13.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+SUMIF(C[-1],RC[-1],C[-9])"
dng13.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-4],RC[-3])"
Sheets("CRIS").Select


dng6.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+MATCH(RC[-1],'MAY FY20 IDARRS'!C[12],0)"

dng17.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+DATE(YEAR(TODAY()),MONTH(TODAY()),1)"


dng18.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+INDEX('MAY FY20 IDARRS'!C[-1]:C[47],CRIS!RC[35],43)"

dng19.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "CRIS"

dng20.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "2 - ALC 5700"

dng21.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+INDEX('MAY FY20 IDARRS'!C[-4]:C[44],CRIS!RC[32],22)"

dng22.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+INDEX('MAY FY20 IDARRS'!C[-5]:C[43],CRIS!RC[31],1)"

Cells.Select
Range("AC1").Activate
Selection.AutoFilter
Sheets("MAY FY20 IDARRS").Select

dng5.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "COMPLETE"

dng7.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "CRIS"

End Sub


hey guys i have the above code. I will fix where the formulas and what ranges go i got a little ocnfused with 22 of them. My problem is that it is only inserting it into the top row. I want it to insert into every row that is visble beneath the headers row. Any ideas why this isnt working?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

I think it is to do with

VBA Code:
Xrow = Cells(Rows.Count, "K").End(xlUp).Row

being just after add sheet CRIS. When you add a new sheet it is blank, so no cells have data.

As a test, change the above code to

Code:
Xrow = 5

and see if this makes a difference. If so, then you need to get the last row somewhere else.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,235
Messages
5,546,648
Members
410,752
Latest member
MC01_
Top