Solved

Excel VB Scripting

Posted on 2013-01-14
8
628 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Michael Fowler
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

635 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