updating summary sheet with values from multiple sheets in difference cells

1
votes

I am quite new to VBA, I have attempted to write a code below, but I'm getting a type mismatch error (Highlighted below). What I am trying to achieve is the following:

I have a list of properties on column A (all hyperlinked to their own respective sheets) on the first summary sheet called "sheet"

  1. For each property, go to that properties sheet
  2. Copy the value 3 cell right to the cell with the string "Total for this Property"
  3. Switch back to "sheet" and paste the value into column D, next to the corresponding property name in column A.

I think the issue is the way I am referencing the value in the other sheet, but I can't seem to find anywhere how to reference a value that is located somewhere relative to a cell with a specific text.

Thank you in advanced!

Sub Summary()

    Dim MasterBook As Workbook
    Dim Sht As Worksheet
    Dim Rng, Rng2 As Range

    Set MasterBook = ThisWorkbook
    Set Sht = MasterBook.Worksheets("Sheet")
    Set Rng = Sht.Range("A6:A" & Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row)

    Dim Cell As Range

    For Each Cell In Rng
    Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Cell.Offset(0, 3).Value = Cell.Value("Total for this Property").Offset(0, 3).Value  '<---- This line is giving the error

    Next Cell
End Sub
4
vbaexcel
Your point 2 isn't clear to me, and your syntax on that line is wrong. Can you elaborate as to your expected results? - SJR
Thank you for the quick response. What I meant is: For example: if cell A6 is called "123 Dan street" then open the sheet 123 Dan street and look for the cell with the text "Total for this property". in this example "Total for this property" text is located in cell A1920. So I want to copy the value, 3 cells right to it, so D1920 and then switch back to the summary sheet. and in column D paste the value (same row as "123 Dan street", so D6). I hope its clearer. - kira123
Yes I think that's clearer. I have posted a suggested solution so let me know how you get on. You were missing the bit to tell Excel to look for the text. - SJR
@kira123 you should accept SJR answer, since it's the correct one - Shai Rado

4 Answers

4
votes

Try this

Sub Summary()

Dim MasterBook As Workbook
Dim Sht As Worksheet
Dim Rng As Range, Rng2 As Range, Rng3 As Range

Set MasterBook = ThisWorkbook
Set Sht = MasterBook.Worksheets("Sheet")
Set Rng = Sht.Range("A6:A" & Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row)

Dim Cell As Range

For Each Cell In Rng
    Set Rng3 = MasterBook.Sheets(Cell.Text).Cells.Find(What:="Total for this Property", LookIn:=xlValues, LookAt:=xlWhole, _
                 MatchCase:=False, SearchFormat:=False)
    If Not Rng3 Is Nothing Then
        Cell.Offset(0, 3).Value = Rng3.Offset(, 3).Value
    Else
        MsgBox "not found"
    End If
Next Cell

End Sub
3
votes

It's no wonder that this line is giving you trouble:

Cell.Offset(0, 3).Value = Cell.Value("Total for this Property").Offset(0, 3).Value

The Value property doesn't take arguments like this! I assume you are trying the find the cell with the text Total for this Property and offset 3 from it?

In that case you should look into the Find command. 

Dim myFoundCell as Range
Set myFoundCell = ActiveWorkbook.ActiveSheet.UsedRange.Find(what:="Total for this Property", lookat:=xlWhole)
If Not myFoundCell Is Nothing Then
    Cell.Offset(0, 3).Value = myFoundCell.Offset(0,3).Value
End If

I think you'll also have trouble handling which workbook you're in (the ActiveWorkbook) after following a load of hyperlinks, so look into ActiveWorkbook and ThisWorkbook. What I mean is, that each range must be within a sheet on a given workbook. You're trying to copy across workbooks so will have to specify where data is going to / coming from.

Docs for Find: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

Another question when you get stuck perhaps.

1
votes

I guess you're after this:

Sub Summary()
    Dim Cell As Range, foundCell As Range

    With ThisWorkbook.Worksheets("Sheet")
        For Each Cell In .Range("A6", .Cells(.Rows.count, "A").End(xlUp))
            Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
            Set foundCell = ActiveSheet.UsedRange.Find(what:="Total for this Property", lookat:=xlWhole, LookIn:=xlValues)
            If Not foundCell Is Nothing Then Cell.Offset(0, 3).Value = foundCell.Offset(0, 3).Value  '<---- This line is giving the error
        Next Cell
        .Activate
    End With
End Sub
0
votes

Since you want to "fetch" the value from a certail Worksheet (which name is placed in the cell) and not in the entire Workbook, you need to use the Find for that specific Worksheet.

The Find method syntax for that is :

Set FindRng = Worksheets(Cell.Value).Cells.Find("Total for this property")

Sub-loop Code:

Dim Cell As Range, FindRng As Range

For Each Cell In Rng
    Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

    Set FindRng = Worksheets(Cell.Value).Cells.Find("Total for this property")

    If Not FindRng Is Nothing Then    ' verify that Find was successful
        Cell.Offset(0, 3).Value = FindRng.Offset(0, 3).Value          
    End If
Next Cell