CODEWITHSUNDEEP
excelvba
UnknownUser119
UnknownUser119

Reputation: 5

Retrieve a value from a different worksheet than the current one

I want to retrieve a value from a worksheet other than the current one.

'Variables at the top of the code
Dim workBookThis As Workbook 'This Workbook

'Dim WBN As Workbook 'New workbook

Dim activeSheet As Worksheet 'Data

Dim hourSheet As Worksheet 'Lookup sheet

'Dim WSR As Worksheet 'Report worksheet, in WBN

Set workBookThis = Workbooks("SQE Workload Estimates Test Copy.xlsm")

Set activeSheet = workBookThis.Worksheets(sheetName)

Set hourSheet = workBookThis.Worksheets("Hours Breakdown")

' -------------------------------------------------------------------------------

'Part of the code not functioning properly.
Dim hours As Integer

'hours = 120
            
'Dim hourSheet As Worksheet
            
'Set hourSheet = ThisWorkbook.Sheets("Hours Breakdown")
            
Dim combinedIndividual As Integer
            
'Start of for loop which will run from csIndividual up to 20.
For combinedIndividual = 0 To 20
    On Error Resume Next

    'Start of if statement which says if the cell's value three cells to the left and up until it hits a non-blank cell of the Target cell is equal to ESQ.
    If Cells(rowPos - combinedIndividual, colPos - 2).Value = "Combined System" And _
      Cells(rowPos - combinedIndividual, colPos - 1).Value = "ESQ" Then
                
        hours = ThisWorkbook.Sheets("Hours Breakdown").Range(Cells(135, 2)).Value
        'hours = 12
        'MsgBox "Combined System, ESQ"
        Exit For
                    
    ElseIf Cells(rowPos - combinedIndividual, colPos - 2).Value = "Combined System" And _
      Cells(rowPos - combinedIndividual, colPos - 1).Value = "Legacy" Then
                
        'hours = ThisWorkbook.Sheets("Hours Breakdown").Range(Cells(136, 2)).Value
        hours = 16
        'MsgBox "Combined System, Legacy"
        Exit For
                    
    End If
                    
    'Start of if statement which says if the cell's value three cells to the left and up until it hits a non-blank cell of the Target cell is equal to ESQ.
    If Cells(rowPos - combinedIndividual, colPos - 2).Value = "Individual" And _
        Cells(rowPos - combinedIndividual, colPos - 1).Value = "ESQ" Then
                
        'hours = ThisWorkbook.Sheets("Hours Breakdown").Range(Cells(136, 2)).Value
        hours = 20
        'MsgBox "Individual, ESQ"
        Exit For
                    
    ElseIf Cells(rowPos - combinedIndividual, colPos - 2).Value = "Individual" And _
      Cells(rowPos - combinedIndividual, colPos - 1).Value = "Legacy" Then
                
        'hours = ThisWorkbook.Sheets("Hours Breakdown").Range(Cells(136, 2)).Value
        hours = 24
        'MsgBox "Individual, Legacy"
        Exit For
                    
    End If
                
Next combinedIndividual
            
MsgBox hours

I need to change the value of hours based on the criteria within the If statements. Every attempt has come up with 0.

I seem to have declared the workbook and sheets as their own variables, and I'm using code that has worked in other situations. It may not be the prettiest, but it functions.

Upvotes: 0

Views: 58

Answers (1)

HTH
HTH

Reputation: 2031

here's a revision of your code that could possibly solve your issue

all revisions but one are commented, so you can follow them one by one

the uncommented one is that I changed all If Then EledIF EndIf construct into Select Case ones, which I think gives more readability

'Variables at the top of the code
Dim sheetName As String
Dim workBookThis As Workbook 'This Workbook

Dim dataSheet As Worksheet 'Data
Dim hourSheet As Worksheet 'Lookup sheet

'Dim WSR As Worksheet 'Report worksheet, in WBN

Set workBookThis = ThisWorkbook

Set dataSheet = workBookThis.Worksheets(sheetName) ' be sure 'sheetName' variable has been assigned a name of an existing worksheet in 'workBookThis' workbook
Set hourSheet = workBookThis.Worksheets("Hours Breakdown")

Dim hours As Long, rowPos As Long, colPos As Long, combinedIndividual As Long ' use Long types instead of 'Integer ones. Long span up to some +/- 2 billions while integers are limited tu some +/-32k

'Start of for loop which will run from csIndividual up to 20.
With dataSheet ' reference "data" sheet

    For combinedIndividual = 0 To 20

        With .Cells(rowPos - combinedIndividual, colPos - 2) ' reference referenced sheet cell at 'rowPos - combinedIndividual' row index and 'colPos - 2' column index
        'Start of if statement which says if the cell's value three cells to the left and up until it hits a non-blank cell of the Target cell is equal to ESQ.
            Select Case .Value ' check referenced cell value

                Case "Combined System"
                    Select Case .Offset(, -1).Value ' check referenced cell one column to the right value
                        Case "ESQ"
                            hours = hourSheet.Cells(135, 2).Value
                            Exit For

                        Case "Legacy"
                            hours = 16
                            Exit For

                    End Select


                Case "Individual"
                    Select Case .Offset(, -1).Value
                        Case "ESQ"
                            hours = 20
                            Exit For

                        Case "Legacy"
                            hours = 24
                            Exit For

                    End Select
            End Select

        End With

    Next

End With

MsgBox hours

let me know

Upvotes: 1

Related Questions