ActiveCell.value does not display anything

[pwr1965]

I have a number of values, originating from a given worksheet, that need to be displayed on another worksheet. This works great for most values with the following code:

Sheets("Invulblad").Select
Range("M4").Select
Selection.End(xlDown).Select
Sheets(Sheets.Count).Range("B13") = ActiveCell.Value

Here's the problem: it does not work if the active cell contains a formula. On the source worksheet I can see the formula result, but VBA does not copy the resulting value to its destination. Ideas anyone?

 

[Joe4]

Hi Joe4,
Thanks a million, this makes my code a lot leaner. Alas, it did not solve my initial problem: all source cells containing formulas result in empty destination cells..

My guess is your code is working exactly as it should, you just don't realize that you have a formula returning a blank cell in there somewhere.

Try adding this code above what your currently have, and see what it returns:

Dim r as Long
r = Sheets("Invulblad").Range("M4").End(xlDown).Row
MsgBox "Returning value from row " & r

Then verify what is in column M of that row on your "Invulblad" sheet.
That is what is your code is returning.

 

[pwr1965]

Yes, that I have.

In that case using

Range("M4").Select
   Selection.End(xlDown).Select

Will take you the very last row in col M that has a formula & it's probably returning ""

You're totally right. Thing is, I need the formulas there to do the VLOOKUP thing. Is there a VBA equivalent for this function?

 

[pwr1965]

My guess is your code is working exactly as it should, you just don't realize that you have a formula returning a blank cell in there somewhere.

Try adding this code above what your currently have, and see what it returns:

Dim r as Long
r = Sheets("Invulblad").Range("M4").End(xlDown).Row
MsgBox "Returning value from row " & r

Then verify what is in column M of that row on your "Invulblad" sheet.
That is what is your code is returning.

You're right (Fluff already posted something similar). The thing is: based on an article code that is input on "Invulblad", a VLOOKUP formula fills several other cells in that particular row with stuff like a job description and a price. This formula is copied down in column M, so it works for each new row. Now, thanks to you guys, I understand how this throws a spanner in the works of my VBA code. Is there a way around this?

 

[Fluff]

How about

   Dim Fnd As Range
   With Sheets("Invulblad")
      Set Fnd = .Range("M4:M10000").Find("*", , xlValues, , xlByRows, xlPrevious, , False)
      If Not Fnd Is Nothing Then
         Sheets(Sheets.Count).Range("B13").Value = Fnd.Value
      End If
   End With

Edited to show correction mentioned below

 

[pwr1965]

How about

   Dim Fnd As Range
   With Sheets("Invulblad")
      Set Fnd = .Range("M4:M10000").Find("*", , xlValues, , xlByRows, xlPrevious, , False)
      If Fnd Is Not Nothing Then
         Sheets(Sheets.Count).Range("B13").Value = Fnd.Value
      End If
   End With

Hi Fluff,
This returns a compiler error; sit marks 'Nothing' as invalid.

 

[Fluff]

Oops, that line should be

      If Not Fnd Is Nothing Then

 

[pwr1965]

Oops, that line should be

      If Not Fnd Is Nothing Then

Thanks! Working beautifully now!

 

[Fluff]

Glad we could help & thanks for the feedback.