Solved

Excel VB Scripting

Posted on 2013-01-14
8
618 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
ID: 38777006
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
ID: 38777011
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
ID: 38777045
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 16

Accepted Solution

by:
terencino earned 250 total points
ID: 38777841
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
 

Author Comment

by:kkenison
ID: 38778357
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
ID: 38778481
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
ID: 38778830
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
ID: 38778859
Thanks, kkenison.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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