VBA Excel - Error checking cell value on cells containing TRANSPOSE forumla

Hi,

I have a range of cells that I need to check if they are empty or 0.

The cells contain a formula "=TRANSPOSE(''A2:JK8)"

As a result, any time I try to check the cell value or modify the cell value I seem to get "type mismatch" errors.

If Range("A2:JK8").value = "" Then

Open in new window


Can anybody help me get around this?

I attempted to convert the cell to a value (and remove the formula) but I got an error "cannot change part of an array".
Range("A2:JK8").value = Range("A2:JK8").value

Open in new window


Thankyou
mhdiAsked:
Who is Participating?
 
SteveCommented:
Which cells contain the TRANSPOSE formula, it will not just be one cell but an array of cells.

You need to work with the array of cells holding transpose formula.

Simple Example workbook attached.

To change the cells to values use:

Sheets("Sheet2").Range("B2:H283").value = Sheets("Sheet2").Range("B2:H283").value

Open in new window

Transpose-VBA-Example.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.