Printable View
Hi,
I have this worksheet Snap shotwhich is linked to another sheet.
every day I need to replace the raw file with new data and hence the row number and column number changes as well.
For example --
last week the formula was
=COUNTIF(dummy1!$Q$6:$Q$44,1)
This week it may change to
=COUNTIF(dummy1!$Q$16:$Q$48,1)
Now I need to change it for all cells,please advice a fast find and replace,so that I can do all replaces at once.
ctrl f -> replace:
find what: =COUNTIF(dummy1!$Q$6:$Q$44,1)
replace with: =COUNTIF(dummy1!$Q$6:$Q$48,1)
replace all
Its not working for rows
for example I have multiple rows ----
=COUNTIF(dummy1!$Q$6:$Q$44,1)
=COUNTIF(dummy1!$N$6:$N$44,1)
=COUNTIF(dummy1!$D$6:$D$44,1)
and I want to change them to
=COUNTIF(dummy1!$Q$12:$Q$49,1)
=COUNTIF(dummy1!$N$12:$N$29,1)
=COUNTIF(dummy1!$D$12:$D$49,1)
As a quick fix I would use named ranges on sheet dummy1 which would contain the rows you need for each area. You can then extract the upper and lower row bounds of the ranges to use in a VBA find and replace routine.
If you want to get a bit more automated you could set the row ranges from VBA code, using .FIND as a lookup to get the column and change the .FORMULA property directly. If you write this into the Worksheet_Activate event of the "snap shot" sheet module then it would automatically update when you select that sheet.
Sorry I don't have time to give a full example but the concepts are in the code below
[vba]Dim lLowerbound As Long
Dim lUpperbound As Long
Dim sName As String
Sub test()
With Sheets("dummy1")
'first group of data. Loop through until lLowerbound = 6
'I would load this into either a group or arrays or a dictionary object
lUpperbound = Range("B" & .Rows.Count).End(xlUp).Row
lLowerbound = Range("A" & lUpperbound).End(xlUp).Row
sName = Range("A" & lLowerbound).Value
End With
With Sheets("snap shot")
'random location to show the method only
.Range("J5").Formula = "=COUNTIF(dummy1!$Q$" & lLowerbound & ":$Q$" & lUpperbound & ",1)"
End With
End Sub[/vba]
[vba]Sub SnapShotRefreshFormulas()
Dim WB As Workbook
Dim WSdummy As Worksheet
Dim WSSnapshot As Worksheet
Dim LastRow As Long
Dim TopRow As Long
Dim BtmRow As Long
Dim A As Long
Dim B As Long
Dim C As Long
Dim iCol As Long
Dim iRow As Long
Dim TblRow As Long
Dim MyCols As String
Dim aCell As Range
Set WB = ActiveWorkbook
Set WSdummy = WB.Worksheets("dummy1")
Set WSSnapshot = WB.Worksheets("Snap shot")
With WSdummy
'Lastrow of Dummy1 sheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'The ranges are fixed. If rows/columns change, you'll
'have to update these.
For Each aCell In WSSnapshot.Range("C2,C13,C25,C37,C49")
'Row index for table name plus three rows, the start of the data.
TblRow = aCell.Row + 3
'Find Region name in column a
TopRow = Application.WorksheetFunction.Match(Trim(aCell), .Range("A1:A" & LastRow), 0)
'Find next non-empty cell. This should be the end of the Region.
BtmRow = .Cells(TopRow + 1, "A").End(xlDown).Row - 1
'Alt .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues)
'=COUNTIF(dummy1!$Q$6:$Q$44,1)
'These are a list of Column letters that are used in the formula.
MyCols = "QRSNOPDEFGHIJKLMNO"
'Step through column letters.
For A = 1 To Len(MyCols) Step 3
'Counters for three month spread.
For B = 0 To 2
'CountIf lookup value.
For C = 1 To 0 Step -1
'Insert the formula.
WSSnapshot.Cells(TblRow + iRow, 3 + iCol).Formula = _
"=COUNTIF(dummy1!$" & Mid(MyCols, A + B, 1) & _
"$" & TopRow & ":$" & Mid(MyCols, A + B, 1) & "$" & _
BtmRow & "," & C & ")"
'Increment the table column.
iCol = iCol + 1
Next
Next
'increment the table row. Reset Column counter.
iRow = iRow + 1
iCol = 0
Next
'Reset row counter.
iRow = 0
Next
End With
End Sub
[/vba]