Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VB Scripting

Posted on 2013-01-14
8
Medium Priority
?
630 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 1000 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 16

Accepted Solution

by:
terencino earned 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 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