Subscript Out of Range

R

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
Can anyone tell me why I'm getting the "Subscript out of Range" error on the line in red font? I have a feeling it has something to do with the "ThisWorkbookName = ActiveWorkbook.Name" line, but I'm not sure, as I have the same code structure on a different form, that doesn't give me the same error.

Ideally, I'd like a way to set some sort of code that I can use in any module that says the "core" workbook is always a certain value (maybe "BBT"), and the other workbook in the picture is always "CS".

Code: 
Private Sub cmd_Submit_Click()

Application.ScreenUpdating = False


Dim CS As Workbook
Dim ws4, CSWS6, CSWS7 As Worksheet
Dim LastRow4, CSLastRow6, CSLastRow7, CSUpdateRow7 As Long
Dim CSFindRow7 As Range


ThisWorkbookName = ActiveWorkbook.Name


Set ws4 = ThisWorkbook.Sheets("Pymt Tracker")
[COLOR=#ff0000]Set CS = Workbooks(Me.txt_ClientID.Text)[/COLOR]
Set CSWS6 = CS.Sheets("Pymt Tracker")
Set CSWS7 = CS.Sheets("Financials")
LastRow4 = ws4.Range("C" & Rows.Count).End(xlUp).Row


CS.Activate
CSLastRow6 = CSWS6.Range("C" & Rows.Count).End(xlUp).Row
CSLastRow7 = CSWS7.Range("D" & Rows.Count).End(xlUp).Row
With ActiveSheet
    Set CSFindRow7 = Range("CB:CB").Find(What:="Late", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    If Not CSFindRow7 Is Nothing Then
        CSUpdateRow7 = CSFindRow7.Row
    Else
        CSUpdateRow7 = CSLastRow7
    End If
End With


CSWS7.Range("B" & CSUpdateRow7).Value = Now()
CSWS7.Range("C" & CSUpdateRow7).Value = "Update"
If Not Len(Me.txt_Wk1) = 0 Then CSWS7.Range("BT" & CSUpdateRow7).Value = CCur(Me.txt_Wk1)
If Not Len(Me.txt_Wk2) = 0 Then CSWS7.Range("BU" & CSUpdateRow7).Value = CCur(Me.txt_Wk2)
If Not Len(Me.txt_Wk3) = 0 Then CSWS7.Range("BV" & CSUpdateRow7).Value = CCur(Me.txt_Wk3)
If Not Len(Me.txt_Wk4) = 0 Then CSWS7.Range("BW" & CSUpdateRow7).Value = CCur(Me.txt_Wk4)
If Not Len(Me.txt_Wk5) = 0 Then CSWS7.Range("BX" & CSUpdateRow7).Value = CCur(Me.txt_Wk5)


CSWS6.Range("A" & CSLastRow6 + 1) = "=Today()"
CSWS6.Range("B" & CSLastRow6 + 1) = Now()
CSWS6.Range("C" & CSLastRow6 + 1) = Me.txt_ClientID
CSWS6.Range("D" & CSLastRow6 + 1) = Me.txt_Name
CSWS6.Range("E" & CSLastRow6 + 1) = Me.cobo_Nickname
CSWS6.Range("F" & CSLastRow6 + 1) = CDate(Me.txt_DateRcvd)
CSWS6.Range("G" & CSLastRow6 + 1) = CCur(Me.txt_AmtRcvd)
CSWS6.Range("H" & CSLastRow6 + 1) = Me.cobo_TotalPymtMethod
CSWS6.Range("I" & CSLastRow6 + 1) = Me.txt_PymtReason


Application.ScreenUpdating = True


MsgBox "The Client's payment has been recorded."


End Sub
Private Sub txt_ClientID_Change()


Dim CS As Workbook
Dim CS7 As Worksheet
Dim CSLastRow7, CSUpdateRow7 As Long
Dim CSFindRow7 As Range


Set CS = Workbooks.Open("C:\Users\Rodger\Desktop\Bodies by Trish\Client Sheets\" & Me.txt_ClientID & ".xlsm")
'Set CS = Workbooks.Open("C:\Users\NBKDA6K\Desktop\Bodies by Trish\Client Sheets\" & Me.txt_ClientID & ".xlsm")
Set CSWS7 = CS.Sheets("Financials")


CS.Activate


With CS.Sheets("Financials")
    CSLastRow7 = CSWS7.Range("D" & Rows.Count).End(xlUp).Row
    Set CSFindRow7 = Range("CB:CB").Find(What:="Late", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    If Not CSFindRow7 Is Nothing Then
        CSUpdateRow7 = CSFindRow7.Row
    Else
        CSUpdateRow7 = CSLastRow7
    End If
End With
On Error Resume Next
With CS.Sheets("Financials")
    Me.txt_Name = .Range("E" & CSUpdateRow7).Value
    'Me.cobo_Nickname = .Range("F" & CSUpdateRow7).Value
    Me.txt_DPStatus = .Range("G" & CSUpdateRow7).Value
    Me.txt_DPDelq = .Range("M" & CSUpdateRow7).Value
    Me.txt_TPStatus = .Range("N" & CSUpdateRow7).Value
    Me.txt_TPDelq = .Range("T" & CSUpdateRow7).Value
    Me.txt_TRStatus = .Range("U" & CSUpdateRow7).Value
    Me.txt_TRNextDue = Format(.Range("AA" & CSUpdateRow7).Value, "MM/DD/YY")
    Me.txt_TRAmtDue = .Range("AB" & CSUpdateRow7).Value
    Me.txt_TRDelq = .Range("AC" & CSUpdateRow7).Value
    Me.txt_DCStatus = .Range("AE" & CSUpdateRow7).Value
    Me.txt_DCNextDue = Format(.Range("AK" & CSUpdateRow7).Value, "MM/DD/YY")
    Me.txt_DCAmtDue = .Range("AL" & CSUpdateRow7).Value
    Me.txt_DCDelq = .Range("AM" & CSUpdateRow7).Value
    Me.txt_OCStatus = .Range("AO" & CSUpdateRow7).Value
    Me.txt_OCNextDue = Format(.Range("AU" & CSUpdateRow7).Value, "MM/DD/YY")
    Me.txt_OCAmtDue = .Range("AV" & CSUpdateRow7).Value
    Me.txt_OCDelq = .Range("AW" & CSUpdateRow7).Value
    Me.txt_CTIStatus = .Range("AY" & CSUpdateRow7).Value
    Me.txt_CTINextDue = Format(.Range("BE" & CSUpdateRow7).Value, "MM/DD/YY")
    Me.txt_CTIAmtDue = .Range("BF" & CSUpdateRow7).Value
    Me.txt_CTIDelq = .Range("BG" & CSUpdateRow7).Value
    Me.txt_CTOStatus = .Range("BI" & CSUpdateRow7).Value
    Me.txt_CTONextDue = Format(.Range("BO" & CSUpdateRow7).Value, "MM/DD/YY")
    Me.txt_CTOAmtDue = .Range("BP" & CSUpdateRow7).Value
    Me.txt_CTODelq = .Range("BQ" & CSUpdateRow7).Value
    Me.txt_TotalDue = Format(.Range("BS" & CSUpdateRow7).Value, "#0.00")
    Me.txt_Wk1 = Format(.Range("BT" & CSUpdateRow7).Value, "#0.00")
    Me.txt_Wk2 = Format(.Range("BU" & CSUpdateRow7).Value, "#0.00")
    Me.txt_Wk3 = Format(.Range("BV" & CSUpdateRow7).Value, "#0.00")
    Me.txt_Wk4 = Format(.Range("BW" & CSUpdateRow7).Value, "#0.00")
    Me.txt_Wk5 = Format(.Range("BX" & CSUpdateRow7).Value, "#0.00")
    Me.txt_FundsRcvdToDate = Format(.Range("BY" & CSUpdateRow7).Value, "#0.00")
    Me.txt_InReserve = Format(.Range("BZ" & CSUpdateRow7).Value, "#0.00")
    Me.txt_NetDue = Format(.Range("CA" & CSUpdateRow7).Value, "#0.00")
End With


End Sub
Private Sub UserForm_Initialize()


Dim ws3, ws5 As Worksheet
Dim cBiosNickname, cPymtMethod As Range


ThisWorkbookName = ActiveWorkbook.Name


Set ws3 = ThisWorkbook.Sheets("Bios")
Set ws5 = ThisWorkbook.Sheets("Variables")


On Error Resume Next
For Each cBiosNickname In ws3.Range("BiosNickname")
    With Me.cobo_Nickname
        .AddItem cBiosNickname.Value
    End With
Next cBiosNickname
On Error Resume Next
For Each cPymtMethod In ws5.Range("PymtMethod")
    With Me.cobo_TotalPymtMethod
        .AddItem cPymtMethod.Value
    End With
Next cPymtMethod


Me.txt_Wk1 = "0.00"
Me.txt_Wk2 = "0.00"
Me.txt_Wk3 = "0.00"
Me.txt_Wk4 = "0.00"
Me.txt_Wk5 = "0.00"
Me.cobo_TotalPymtMethod = "Select"
Me.txt_PymtReason = "Payment for services rendered."


End Sub
Private Sub cobo_Nickname_Change()
Dim ws3 As Worksheet
Dim FindRow As Range
Dim NickRow As Long


Set ws3 = ThisWorkbook.Sheets("Bios")
ThisWorkbook.Sheets("Bios").Activate
With ws3
'Finds the row where the value of the cobo_RefNickame exists, and populates the RefID combobox with the value of the Client ID, from the same row.
Set FindRow = Range("J:J").Find(What:=Me.cobo_Nickname, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow Is Nothing Then
    NickRow = FindRow.Row
Else
    Exit Sub
End If
End With
Me.txt_ClientID = ws3.Range("E" & NickRow).Value
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Does txt_ClientID have the name of a workbook, including the file extension, that is currently open in Excel?
 
Upvote 0
@Norie, actually it doesn't. The text box value would be equal to "ND2". When I step through the code, it's pulling the correct file. I reference the file extension when I open the file on the Client ID change routine.
 
Upvote 0
So here's the weird thing. I get to the office and decide to re-run the code, as is. It worked fine. The only difference that I can think of, is that last night I had a 3rd workbook open. I'm not sure why that would matter, as that 3rd workbook isn't referenced anywhere in this code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Subscript out of range when copying values to another sheet
Replies
9
Views
188
ipbr21054
I
B
  • Question Question
Subscript Out of Range - Update Sheet Name (VBA)
2
Replies
10
Views
935
kevin9999
K
B
  • Locked
  • Solved
Subscript Out of Range
Replies
3
Views
712
Fluff
Fluff
L
  • Solved
VBA Collections value - Run time error 9 - subscript out of range
Replies
5
Views
591
Legacy 502047
L
O
  • Solved
VBA Code for Number Format - Run-time error '1004' Unable to set the Number Format property of the Range class
Replies
3
Views
2K
JoeMo
J

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top