"Evaluate" VBA function

[lm^2]

Hello. I am new to VBA and would appreciate some help.

I am writing a simple VBA program to determine if a range of cells has text or a number. Then populate another range of cells (same worksheeet) with the results.

Conditionals: If the cell is text then I want the cell = empty. If the cell is a number then I want the cell to return a 1 if value >0.85 or a 0 if value < 0.85.

The cells to evaluate are: A1 to AS60
The output cells are: AU1 to CM60

Please Login or Register  to view this content.

Error: "Expected: end of statement"

I've been struggling with this error for awhile and would appreciate some help.

[jindon]

try change to

Please Login or Register  to view this content.

[lm^2]

Hello. Thank you for your message.

I tried changing the line to

Please Login or Register  to view this content.

but I am still getting the error "Compile Error: Expected: end of statement"

Should the line read "su60" or "AS60"? I tried both and they both give the same error.

[Marc L]

A bit confusing between the Range.Formula property in order so allocate a formula to a range
and the Evaluate function (a VBA help must read !) which calculates a formula without using any formula in a range !
And as you forgot to double each double quote within the string formula …

So for the formula in a range : [AU1:CM60].Formula = "=IF(ISTEXT(A1),"""",(A1>=0.85)*1)" …

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[jindon]

Should be...

Please Login or Register  to view this content.

[Marc L]

But if you want only the result the VBA beginner easy way :

With [AU1:CM60] 
    .Formula = "=IF(ISTEXT(A1),"""",(A1>=0.85)*1)" 
    .Formula = .Value2 
End With  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[lm^2]

Hello. Thank you very much for the help. I was able to get the program to work (!) using

Please Login or Register  to view this content.

For some reason I couldn't get

Please Login or Register  to view this content.

to output the values in the cells. But the original error was gone.

[lm^2]

Hi. I am trying to subtract 2 ranges of cells that are on the same worksheet and output the results in a range of cells.

Input ranges are: (A1 to AS58) and (AU1 to CM58)
Output range is: (CO1:EG58)
Conditional: if any of the inputs are text, put an empty space in the cell

I am using this line of code:

Please Login or Register  to view this content.

The output CO1:EG58 shows #VALUE!.

Could you please tell me if my line of code is correct?

[jindon]

Perhaps

Please Login or Register  to view this content.

[lm^2]

Thank you jindon. That works!

[jindon]

OK,
Evaluate method;

Evaluate("1+1") equivalent to [1+1] [] are the sort cut notation of Evaluate method.
Difference is Evaluate accepts variables while [] can only accepts static string, so

Please Login or Register  to view this content.

As you can see the difference, you can use [] with the formula used in the cell as it is.
Subscript of Evaluate needs to be surrounded by double quotes.

Please Login or Register  to view this content.

HTH

[lm^2]

Thank you jindon for the information regarding Evaluate and [].

I am struggling with another issue.

I have one worksheet in one file that I would like to copy into another worksheet in another file.

The code that I have below gives me the error: "Script out of range"

Please Login or Register  to view this content.

It correctly converts the .csv file to .xlsx.
However, it gets hung-up right after the line ActiveWorkbook.SaveAs

I would appreciate help with this.

[jindon]

OK.
Evaluate method doesn't work across workbooks.

Few alternatives and the easiest way is to pace a formula but only if you know full and the worksheet name and the range.

Please Login or Register  to view this content.

TEK-CMOS-56_microscope_csv.xlsx need not to be open.

[lm^2]

Thank you jindon for your message above. I'm having trouble understanding how to implement your code.
It might be easier for me if I use simpler file names.

File #1 is test1.xls with Worksheet1: it has cells A1:AS59
File #2 is test2.xls with Worksheet2: copy test1.xls into test2.xls starting at location CO1

I tried this code below and it gives me a "Subscript out of Range" error. I'm sorry but I can't figure out how to correct this based on your example above

Please Login or Register  to view this content.

[jindon]

You can't do like that.
If both workbooks are open

Please Login or Register  to view this content.

By the way. file extension is XLSX, not XLXS.

[lm^2]

Thank you jindon. I was able to get the expression to work with "Workbooks.Open" instead of just "Workbooks" and making your correction "xlsx".

I am having another problem with subtracting a range of cells in the same worksheet, in particular the ranges: (AU1:CM60) - (CO1:EF60)

I am trying the line of code below but it is giving me incorrect values in the cells. Would you have a suggestion how to correct?

Please Login or Register  to view this content.

[jindon]

(AU1:CM60) - (CO1:EF60)

Number of columns are different.

[lm^2]

Thank you jindon. It works now.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.