Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
OldFella

User Input Pop-up to Update VBA String

Question

Guess who again!

A new little challenge - but still on the original theme.

I've renamed the sheet 'Back-test Layout v3' to 'Back-test Template' and I'm now starting to create new workbooks for the multiple data sets that I want to back-test. The first realization is that I'm going to have to go to each of your 4 x subs, and my added sub, and change the sheet name in eight places - on at least 60 worksheets.

To simplify it - are you able to create a user input pop-up, that I can assign to a "Sheet name?" button, into which I can then enter the current sheet name which will replace the string "Back-test Template" in each of the five sub-routines?  Sort of like Find / Replace, but applied to the VBA code instead of to formulae on the worksheet.

Thanks

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Hi OldFella, 

 

Thanks for the question! I like having coding projects to work on! :D Ah, that would indeed be time-consuming to edit the code every time. I would suggest a slightly different approach, I would get VBA to loop all the sheets in the workbook and then give the macro the sheet name  automatically (so you don't need to keep entering them)

 

Please note the following:

  1. The new code MUST be in the 'ThisWorkbook' module.
  2. Now that you have created the All_in_One() macro, you can delete the other small subroutines I had made. 
  3. This will run code on ALL the sheets in the workbook, EXCEPT the 'Back-test Template' sheet (case-sensitive). If you need to exclude more sheet names, I can show you how to edit the code.
  4. This can be modified to run on all sheets of all open workbooks if needed, let me know

I will send you my example file via email :)

Sub All_in_One()
'//Source: www.ExcelWTF.com
'//Purpose: Execute data processing to all sheets within active workbook. Excluding Template sheet.
Dim sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Worksheets
        If Not sh.Name = "Back-test Template" Then
            Call DataProcesser(sh.Name, "SH", "Q", "H", "FO/RV")
            Call DataProcesser(sh.Name, "SL", "Y", "L", "FO/RV")
            Call DataProcesser(sh.Name, "SH", "AG", "I", "FO")
            Call DataProcesser(sh.Name, "SL", "BN", "M", "FO")
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub

Function DataProcesser(wsBT As String, sYesNo As String, sYesNoCol As String, sOutputCol As String, sType As String)
'//Source: www.ExcelWTF.com
'//Purpose: To process horizontal data entries and transpose them with set criteria
    On Error GoTo out
    Dim ws As Worksheet
    Dim rYesNO As Range, r As Range
    Dim lr As Long, items As Long, i As Long
    Dim sItem As String
    
    Set ws = Sheets(wsBT)
    ws.Activate
    lr = ws.Range(sYesNoCol & Rows.Count).End(xlUp).Row
    Set rYesNO = ws.Range(sYesNoCol & "3:" & sYesNoCol & lr)
    items = ws.Range(Cells(1, ws.Range(sYesNoCol & 1).Column), Cells(1, ws.Range(sYesNoCol & 1).Column).End(xlToRight)).Count
    
    For Each r In rYesNO
        If r.Value = sYesNo Then
            For i = 0 To items - 1
                sSeq = ws.Range(sYesNoCol & r.Row).Offset(, i + 1).Value
                If sType = "FO" Then
                    If sSeq = "FO" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                Else
                    If sSeq = "RV" And sOutput = "RV" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "RV"
                    ElseIf sSeq = "RV" And sOutput = "FO" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                    ElseIf sSeq = "FO" And sOutput = "RV" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                    ElseIf sSeq = "FO" And sOutput = "FO" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                    ElseIf sOutput = "" And sSeq = "FO" Or sSeq = "RV" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = sSeq
                    End If
                End If
            Next i
        End If
    Next r
    
    ws.Range(sOutputCol & ws.Range("G" & Rows.Count).End(xlUp).Offset(1).Row & ":" & sOutputCol & ws.Range(sOutputCol & Rows.Count).End(xlUp).Offset(1).Row).ClearContents

Exit Function
out:
MsgBox "Ooops... looks like something went wrong. Please check code/output", vbOKOnly + vbExclamation, "Error"
End Function

Let me know if that works. If you still would prefer the input box let me know and I'll code something up for you :D

 

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

It took me a little while to get my head around what you were proposing - even with starting at 0430 hr this morning, when I was supposedly bright-eyed and bushy-tailed! - but I eventually saw what you were thinking. And, as I should have anticipated, it works a treat!

I've spent the day making use of the new code. I've also done a lengthy but badly needed re-vamp of my main worksheet. Previously, using my manual system, it typically took me about three days to cover a year of back-testing. Today I've covered eight years! A month's work completed in a day! And better, I'm not brain-dead as I used to be with the previous method. I'd put more beers here but I don't want you getting drunk!

Once again - grateful thanks for all your help.

 

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×