(Solved) – VBA .Find Inner syntax and storage of Range Values

  • by
(solved)-–-vba.find-inner-syntax-and-storage-of-range-values

Greeting everyone, this is part of a bigger code that both filters based on input text and then creates subtotals out of the values associated with warranty subtypes, this is applied to several different sheets and it all works wonders.

Since there’s the possibility of MANY different Warranties subtypes being present, I proceed to check for each one individually, first for an exact match case for “WarrantyPrefA Total” (this should be on the AJ Column) if it exists, I want it to store that range value inside a variable(GaRangeID), so that I can apply an offset of that range to grab the 2 numerical values present in other columns and paste it on another Workbook. If it Doesn’t exist, I want it to terminate that find, and proceed to find another exact match case. But its not working. My guess is i’m messing up the .find inner syntax to search in the incorrect range.

Dim GaRangeID As Range
Dim WBModeloA1 As Worksheet
Dim WBModeloA2 As Worksheet
Set WBModeloA1 = Workbooks("ModeloAnalisis.xlsm").Sheets("Cartera 1")
Set WBModeloA2 = Workbooks("ModeloAnalisis.xlsm").Sheets("Cartera 3")

‘GPB

   Set GaRangeID = Cells.Find(What:="WarrantyPrefA Total", After:=ActiveCell, LookIn _
   :=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)

If Not GaRangeID Is Nothing Then

   WBModeloA1.Range("E67") = GaRangeID.Offset(0, -3).Range("A1")
   WBModeloA1.Range("E67").Value = WBModeloA1.Range("E67").Value / 1000


   WBModeloA2.Range("H91") = GaRangeID.Offset(0, -21).Range("A1")
   WBModeloA2.Range("H91").Value = WBModeloA2.Range("H91").Value / 1000

Else
End If

‘GPA

   Set GaRangeID = Cells.Find(What:="WarrantyPrefB Total", After:=ActiveCell, LookIn _
   :=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)

If Not GaRangeID Is Nothing Then

   WBModeloA1.Range("E65") = GaRangeID.Offset(0, -3).Range("A1")
   WBModeloA1.Range("E65").Value = WBModeloA1.Range("E65").Value / 1000

   WBModeloA2.Range("H90") = GaRangeID.Offset(0, -21).Range("A1")
   WBModeloA2.Range("H90").Value = WBModeloA2.Range("H90").Value / 1000

Else
End If

‘the reason I show it repeats the same structure but with another find afterwards i’ts because I used to have the “find” part defined in another way.

The following way it properly pastes the subtotals onto the other workbook, but I discarded it since it always sets the GaRangeID as the active cell, when the search gets nothing, the active cell remains as the old subtotal found, and so it just pastes the values of WarrantyA onto B.

Cells.Find(What:="WarrantyPrefB Total", After:=ActiveCell, LookIn _
    :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Set GaRangeID = ActiveCell

‘If you could help me fix either one, or if you have a more elegant solution, I’ll owe you one 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *