• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

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
0
mhdi
Asked:
mhdi
1 Solution
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now