CODEWITHSUNDEEP
vbaexcelcountif
miss Pi
miss Pi

Reputation: 1

Why does the countif function only works if I am in the same worksheet?

I hope someone can help me. I've got two columns with reference numbers in column C and N in one worksheet in excel.

column C:
AAAA1
ABAA3
KDFG4
...

Column N:
ABAA2
AAAA4
MMMM3 ...

Then I add the formula 'left' into the columns A and L to cut off the right number from the reference numbers in columns C and N : 

 Dim lastrow As Long  
  Worksheets(sheet).Cells(2, column - 1).Formula = "=LEFT(" & Worksheets(sheet).Cells(2, column - 1).Offset(0, 2).Address(0, 0) & ",6)"  
lastrow = Worksheets("AAL").Cells(Worksheets(sheet).rows.Count, column).End(xlUp).row  
Worksheets(sheet).Cells(2, column - 1).Resize(lastrow - 1).FillDown

column A:
AAAA
ABAA
KDFG
...

Column L:
ABAA
AAAA
MMMM ...

Afterwards I use the following code to add the missing reference numbers from the column L at the bottom of column A:

 Worksheets(sheet).Calculate  
Dim Rw As Long, Rw2 As Long, iFound As Integer  
Rw2 = Worksheets(sheet).Cells(Worksheets(sheet).rows.Count, 3).End(xlUp).row + 1  

For Rw = 2 To Worksheets(sheet).Cells(rows.Count, 14).End(xlUp).row  
 iFound = WorksheetFunction.CountIf(Range("A:A"), Worksheets(sheet).Cells(Rw, 12))  
 If iFound = 0 Then  
   Worksheets(sheet).Cells(Rw2, 1).Value = Worksheets(sheet).Cells(Rw, 12).Value   
   Rw2 = Rw2 + 1  
 End If  
Next Rw

The weird thing is that if I have the worksheet 'sheet' open, the code works absolutely fine. However if I am on a different worksheet when I run the VBA code, the code adds all values from column L at the bottom of column A, not just 'MMMM'.

It looks like the formula left from the first query doesn't properly calculate unless you open the excel sheet. But I thought the code 

Worksheets(sheet).Calculate

will do exactly that.

Thank you so much for your help!

Upvotes: 0

Views: 273

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Worksheets(sheet).Cells(2, column - 1).Offset(0, 2).Address(0, 0) will only return the cell range and will not include the sheet reference.

You need to add the external reference:

Worksheets("sheet").Cells(2, Column - 1).Offset(0, 2).Address(0, 0, xlA1, 1)

Upvotes: 1

Related Questions