Find & Replace using VBA

[mcdae1]

Hi Everyone,

I'm new to the forum and also a relative novice with Excel VBA. I'm trying to speed up the process of changing names in a energy modeling software that I use (eQuest) by going to the back-end code. I've tried to record a macro with find & replace, but it wasn't really saving me time. To be specific, I need to take the name of a space/room and use it to help name a zone that the space is associated with. I would also like to add a prefix to the zone name such as "TZ_" to differentiate it from the space/room name. For example, let's say I have an office called "Office 215", then I want to find the zone that is associated with that space and change it to "TZ_Office 215." I'm attaching a file (HVAC_Code.xls) which consists of the spreadsheet that I have been testing with. What I would like help with is how do you create VBA program/code that takes the space name (see cell A29), adds a prefix "TZ_" and then replaces the existing zone name (see cell A20). Please note that I also have to preserve some keywords that the energy modeling program requires after the names, i.e. = ZONE or = SPACE. Any information, guidance you can provide to educate me on how to do this would be greatly appreciated.

Thanks,

-Eric

[NickyC]

Hi
it is relativeley easy to find each cell containing the word "space" and extract the text to the right of the "=" sign then add "TZ_" in front. Is this what you want? If so, where do you want the new text "TZ_Office 215" returned?

[NickyC]

.... for example, this code puts yout TZ formula 8 cells to the right of any cell containing "SPACE"

Please Login or Register  to view this content.

[mcdae1]

Hi NickyC,

Thank you for your help. I thought this would be easy for someone who knows VBA. So what I would like the routine to do is to change the name before the keyword ZONE (see cell A29), by using the SPACE name preceeding it (cell A20) and adding a prefix of "TZ_" to the new Zone name. I'm attaching another spreadsheet (TZ_Name_Table) which is my attempt to take the space name and create a zone name. It would be great if I could use this table in a VBA routine to change each corresponding ZONE name in the back-end code. I really appreciate your help and guidance.

-Eric

[NickyC]

Hi

this version replaces the first "= ZONE" string above a "SPACE =" string with a string comprising TZ_ and the space information. Is that what you want?

I have also added " = ZONE" at the end of the text string - delete this bit of code if you don't want it

Please Login or Register  to view this content.

[mcdae1]

NickyC,

This is exactly what I was hoping to accomplish. There is no way I could have done this without your help. Much appreciated.

Thanks,

-Eric