Help automating updating columns (find and replace) with macros.

S

Stacker

Board Regular
Joined
Jul 11, 2021
Messages
87
Office Version
  1. 365
Platform
  1. Windows
I routinely get files and people here have helped me with automating one component. However I wanna create a macros that allows m to automate the updating of certain worksheets. I am given an excel file with three tabs.

In the first tab i get a lot of hospital data and one of my tasks is to to copy the last column say MG and paste it into the MH. All of the cells in the column follow a certain formula. The top being like so:

=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[270821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)

I have to paste this column into the next and from there ctrl and f and find and replace. So I would find and replace 270821 with 280821 solely in that column.

Is there a way to copy and paste the previous column and then find and replace by incrememnts of 1 day using a macro.

The second and third stuff are fairly quick so I am not bothered
 
Sort by date Sort by votes
I pasted your formala in Excel and it gave me #REF. This means Excel has excepted the formula and started evaluating it.
Be sure to clear the cell's contents in its entirety before you paste your formula.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Test Excel.xlsx
ABCD
1210821220821230821240821
221/08/202122/08/202123/08/202124/08/2021
3141413=SUM(INDIRECT("'\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\["& TEXT(D$2;"ddmmyy")&" HRI.xlsx]Daily sitrep'!$F$8:$F$11"))
Sample
Cell Formulas
RangeFormula
A1:D1A1=A2
A3A3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
B3B3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
C3C3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)


When i hit enter in D3 it fails.
 
Upvote 0
It fails since you apparently missed one of my previous posts ....
GWteB said:
A closer look tells me, where I have the dates on row 1 and the custom formatting (to match the filename) on row 2, you have it the other way around :eek:
In your formula, D$2 must therefore become D$1.
Click to expand...

The TEXT function argues because it doesn't get the format as specified, see the image attached. It expects D2 to be formatted as ddmmyy (but it isn't ... ).
Changing D$2 to D$1 should resolve this issue, since D1 is formatted as specified.

ScreenShot239.jpg
 
Upvote 0
Like I said it before still produces the same error when i use

=SUM(INDIRECT("'\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\["& TEXT(D$1;"ddmmyy")&" HRI.xlsx]Daily sitrep'!$F$8:$F$11"))
 
Upvote 0
Is your list separator character the semicolon ; ?
If not, replace it with a comma ,
 
Upvote 0
GWteB said:
Is your list separator character the semicolon ; ?
If not, replace it with a comma ,
Click to expand...
Yep! Now that's actually has REF!

=SUM(INDIRECT("'\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\["&TEXT(D$1,"ddmmyy")&" HRI.xlsx]Daily sitrep'!$F$8:$F$11"))

However I am not sure if i am missing a space now.
 
Upvote 0
I think a space or something is missing cos when there is an extra or missing space in the formula using the old method ,it produces the same REF! error. I used your formula to refer to the other file in column C3) the same REF! error is produced .

Test Excel.xlsx
ABCD
1210821220821230821240821
221/08/202122/08/202123/08/202124/08/2021
3141413#REF!
4000
5554240821 HRI
6111240821 HRI
Sample
Cell Formulas
RangeFormula
A1:D1A1=A2
A3A3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
B3B3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
C3C3=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)
D3D3=SUM(INDIRECT("'\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\["&TEXT(C$1,"ddmmyy")&" HRI.xlsx]Daily sitrep'!$F$8:$F$11"))
A4A4=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$G$8:$G$11)
B4B4=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$G$8:$G$11)
C4C4=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$G$8:$G$11)
A5A5=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$8:$I$8)
B5B5=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$8:$I$8)
C5C5=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$8:$I$8)
D5D5=TEXT(D$1,"ddmmyy")& " HRI"
A6A6='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[210821 HRI.xlsx]Daily sitrep'!$F$8
B6B6='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[220821 HRI.xlsx]Daily sitrep'!$F$8
C6C6='\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[230821 HRI.xlsx]Daily sitrep'!$F$8
D6D6=TEXT(D$2,"ddmmyy")& " HRI"
 
Upvote 0
When the workbook the formula is referring to is closed, you will always get a #REF. The workbook involved must be open in Excel for the formula to be evaluated.
 
Upvote 0
GWteB said:
When the workbook the formula is referring to is closed, you will always get a #REF. The workbook involved must be open in Excel for the formula to be evaluated.
Click to expand...

Really? Cos the other workbooks are closed and the data is collected from the workbooks
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Macro to find & replace from source table - need to match whole source table string to partial string in workbook
Replies
1
Views
404
Kaper
Kaper
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
442
Anthony47
A
L
  • Question Question
search in cell below previously found cell in VBA
Replies
1
Views
505
ChrisFoster
C
C
  • Question Question
Find and Replace Macro for Excel (If x exists then replace y with x) - For Microsoft Excel
Replies
1
Views
575
HaHoBe
HaHoBe
H
  • Solved
Replace cells in column B based on column A
Replies
5
Views
916
hotpinkleg
H

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top