Solved

Excel VB Scripting

Posted on 2013-01-14
8
610 Views
Last Modified: 2013-01-15
Hello Everyone, Here is my project.  I have an excel spreadsheet that currently has four spreadsheets.  Summary, Sheet 1, Sheet 2, Sheet 3.  

All four spreadsheets have the exact same template the only section that would be different is c9:H44.  What I want to do is go into these individual sheets and replaces the x's with A, B, C... and do on.  Basically the user placed an x if they feel that the criteria was met but I want to be able to track who selected that and since they are all x's I need to change this.  After I get them all changed over, I will copy them over to the summary sheet.  The below code works if there is the same amount of sheets, But there will be a chance that some workbooks will have more or  less.  My question is how do I setup VB script to be able to loop through each worksheet and replace the x's with A, B,C..... until it reaches the end of the worksheets.

Here is the code that I have so far and it works except for the {Sheets("sheet2").Select }if the sheets are not there it breaks.

Thank you very much for helping me.

Sheets("Sheet2").Select
    Range("C9:H44").Select
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Selection.Replace What:="x", Replacement:="A", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets("Sheet3").Select
    Range("C9:H44").Select
    Selection.Replace What:="x", Replacement:="B", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets("Sheet4").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("C9:H44").Select
    Selection.Replace What:="x", Replacement:="C", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   
    Sheets("Summary").Select
   
End Sub
0
Comment
Question by:kkenison
8 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, kkenson.

Please see the code below. It's usually not necessary to select sheets and/or ranges before referencing them so I removed some of that code.

Regards,
Brian.
Sub Update_Sheets()

    If Sheet_Exists("Sheet2") Then
        Range("C9:H44").Replace What:="x", Replacement:="A", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End If
        
    If Sheet_Exists("Sheet3") Then
        Sheets("Sheet3").Range("C9:H44").Replace What:="x", Replacement:="B", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End If
    
    If Sheet_Exists("Sheet4") Then
        Sheets("Sheet4").Range("C9:H44").Replace What:="x", Replacement:="C", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End If
    
    Sheets("Summary").Select
    
End Sub

Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean

If xBook = "" Then xBook = ActiveWorkbook.Name

Sheet_Exists = False

On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next

End Function

Open in new window

0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
This should do what you are after

Michael

Sub ReplaceWithX()

   If CheckSheet("Sheet2") Then
      Sheets("Sheet2").Range("C9:H44").Replace What:="x", Replacement:="A", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
   End If

   If CheckSheet("Sheet3") Then
      Sheets("Sheet3").Range("C9:H44").Replace What:="x", Replacement:="B", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
   End If

   If CheckSheet("Sheet4") Then
      Sheets("Sheet4").Range("C9:H44").Replace What:="x", Replacement:="C", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
   End If
    
   Sheets("Summary").Select
    
End Sub

Function CheckSheet(ByVal sheetName As String) As Boolean

   Dim sht As Excel.Worksheet
   
   On Error Resume Next
   Set sht = ActiveWorkbook.Sheets(sheetName)
   On Error GoTo 0
    
   CheckSheet = IIf(sht Is Nothing, False, True)

End Function

Open in new window

0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 250 total points
Comment Utility
kkenison,

Apologies, line 4 omitted the sheet reference...
Sub Update_Sheets()

    If Sheet_Exists("Sheet2") Then
        Sheets("Sheet2").Range("C9:H44").Replace What:="x", Replacement:="A", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End If
        
    If Sheet_Exists("Sheet3") Then
        Sheets("Sheet3").Range("C9:H44").Replace What:="x", Replacement:="B", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End If
    
    If Sheet_Exists("Sheet4") Then
        Sheets("Sheet4").Range("C9:H44").Replace What:="x", Replacement:="C", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End If
    
    Sheets("Summary").Select
    
End Sub

Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean

If xBook = "" Then xBook = ActiveWorkbook.Name

Sheet_Exists = False

On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next

End Function

Open in new window

BTW, you mentioned Sheets1/2/3 but your code referenced Sheets2/3/4 so I used the latter.

Regards,
Brian.
0
 
LVL 16

Accepted Solution

by:
terencino earned 250 total points
Comment Utility
Hi, this will loop through all the sheets (up to 26 of them, easily increased if you need). Let us know if you need some help to consolidate them all to the Summary sheet
...Terry

Sub ReplaceX()
Dim s As Worksheet, a As String, b As Variant, i As Integer
a = "C9:H44" 'range to check
b = Split("A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z", "|") 'x replacements
i = 0 'array counter
If ActiveWorkbook.Sheets.Count > 27 Then
    MsgBox "Too many sheets - edit this macro"
    Exit Sub
End If
For Each s In ActiveWorkbook.Sheets
    If s.Name = "Summary" Then GoTo keep_going
    s.Range(a).Replace What:="X", Replacement:=b(i), LookAt:=xlPart, MatchCase:=False
    i = i + 1
keep_going:
Next s
End Sub

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:kkenison
Comment Utility
Thanks Everyone for your help, I tried one and it worked but there is one more that i want to try.  The next step is to you combine those into a summary page.  below is the code that i have so far and once again it works as long as i know the sheets that are in the workbook but there are times they will change.

Thanks Again


 Sheets("Summary").Select
    Range("C9").Select
    ActiveCell.FormulaR1C1 = ""
    Range("C9").Select
    ActiveCell.FormulaR1C1 = _
        "='sheet2'!RC&'Person2'!RC&sheet3!RC&sheet!RC"
    Range("C9").Select
    Selection.AutoFill Destination:=Range("C9:C45"), Type:=xlFillDefault
    Range("C9:C45").Select
    Selection.AutoFill Destination:=Range("C9:H45"), Type:=xlFillDefault
    Range("C9:H45").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll Down:=-9
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
kkenison,

The next step is to you combine those into a summary page.
This is moving on from your original question, so please raise it as a new one.

Thanks,
Brian.
0
 

Author Closing Comment

by:kkenison
Comment Utility
Thanks everyone for your help. These all were great suggestions I decided to break up the points because i tried two solutions and they both did what i was asking, Hope this was okay.

Thanks Again
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, kkenison.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now