Forum Discussion
Pop up error: run time error 438 object doesn't support this property or method
Thanks. It's in Sub MISC_DC1_DC2(), by the way.
The cause of the behavior that you describe is because you use After:=ActiveCell in the call to ws1.Cells.Find.
The first time, DC2 is found in C5. The second time, it is found after that cell, so in I5.
If you remove the After part, it'll always find DC2 in C5:
Set DestDC2 = ws1.Cells.Find(What:="DC2", LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)If you always want to find the value in column C, use ws1.Range("C:C").Find(...)
If you always want to find the value in column I, use ws1.Range("I:I").Find(...)
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 cell- ,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".
Find 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*")
