Forum Discussion
Pop up error: run time error 438 object doesn't support this property or method
Pop up error: "run time error 438 object doesn't support this property or method" when finding the first row and the last row containing specific text as for in this case would be "DC2". I would ...
HansVogelaar , apologies for the wrong sub.. thanks for the explanation. Learnt quite a fair bit from you.
By the way, is it possible to execute a for each loop to .find(What:="DC2") and add up all the values that one column next to "DC2" cell?
For example, it will be coded as follows:
Dim DestDC2 as Range
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set ws1 = Sheet1
set ws2 = Sheet2
Set DestDC2 = ws2.Cells.Find(What:="DC2", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
For each cell in DestDC2
ws1.range("A1") = Application.WorksheetFunction.Sum (DestDC2.offset(0, 1))
Next cellFind returns the first matching cell, so DestDC2 is a single cell.
Instead of the For Each ... Next loop, you can use
ws1.Range("A1").Value = Application.SumIf(ws2.Range("J:J"), ws2.Range("I:I"), "DC2*")
- ,thank you! the code works perfectly what i wanted. Thanks for the assistance
Dim DestDC2 As Range Dim ws1 As Worksheet Dim ws2 As Worksheet Dim adr As String Dim sumDC2 As Double Set ws1 = Sheet1 Set ws2 = Sheet2 Set DestDC2 = ws2.Cells.Find(What:="DC2", LookAt:=xlPart) If Not DestDC2 Is Nothing Then adr = DestDC2.Address Do sumDC2 = sumDC2 + DestDC2.Offset(0, 1).Value Set DestDC2 = ws2.Cells.Find(What:="DC2", After:=DestDC2, LookAt:=xlPart) If DestDC2 Is Nothing Then Exit Sub Loop Until DestDC2.Address = adr End If ws1.Range("A1") = sumDC2- , it would be cells which contains DC2 with other text
Do you want to look for cells whose value is DC2 without other text, or for cells whose value contains DC2 with possibly other text?
- i have got another project whereby i need to use the .Find function. "DC2" could be in any column but the next column is always the values. Therefore, i would like to use a for loop to loop through every cells that contains "DC2" and sum up to find the total values under "DC2".
