• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Need to add extra room to conference room xls

This is an xl 2010 doc running in 2003 compatibility mode, I believe due to a macro that was created in 2003. I have an xl workbook with the sheets reflecting each month of 2014 and for each day of the year on each sheet, I am listing our conference rooms to schedule reservations. I need to add a room, named 18 East to each day of the year. It must be located two cells below the extention number of room 18 Central, so for instance on January 1, I will need the words "18 East" (without the quotes) to be in cell A23 and for January 2, I will need the same thing in cell A64 etc. for each day of the year. All sheets are formatted so that the words 18 East will always be two cells below the extension of 18 Central (4152).

1 Solution
Saqib Husain, SyedEngineerCommented:
I have modified your existing macro according to this question. You will have to change the newnumber to whatever it should be before running the macro.

Sub AddConferenceRoom()
Dim ws As Worksheet
Dim cell, rng As Range
Dim LastRoom, NewRoom, NewNumber As String
    Application.ScreenUpdating = False
        Set rng = [A1:A5000]
        LastRoom = "18 Central"
        NewRoom = "18 East"
        NewNumber = "'(1234)"
        For Each ws In ThisWorkbook.Worksheets
            For Each cell In ws.Range("A1:A5000")
                If cell = LastRoom Then
                    cell.Offset(3, 0).Value = NewRoom
                    cell.Offset(4, 0).Value = NewNumber
                End If
            Next cell
        Next ws
    Application.ScreenUpdating = False
End Sub
contrainAuthor Commented:
YES!!!!!!! This is exactly what I needed and worked perfectly. The Expert was able to solve my issue with the information and in all of the correct places. Fantastic solution!!!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now