VBA to identify cell with formula but only referenced

[excelsishya]

Hi all ,

Is there Any code to identify cell with formula but only with referenced ones in a selection?

My aim is to divide selected range of cells by 1000 by checking if cell is hard coded or formula with or without cell reference.

Eg A1 =10000 hard coded
A2=50000 hard coded
A3= 10000 Formula (=9000+1000)
A4= SUM(A1:A3) FORMULA

When i select range A1:A4 and run code A1 to A3 Should be divided by 1000.

Real data is with thousands of rows where in many of cell in between A3 kind of formula is there .

Currently i am using 2 Kind of VBA divider code - one which divides only constant and another only formula by checking if not cellhasformula=true/false

both will not serve my purpose in above case.

Thanks

 

[pgc01]

Hi

A simple approach is to test if the formula has any lettter:

If r.Formula Like "*[a-zA-Z]*" Then

Does this suits your needs?

 

[excelsishya]

Thanks pgc01 for reply.

I am very new to VBA.Could you please incorporate your Line of Code to existing code posted below which i am currently using?

Sub divder()
For Each cell In Selection
If Not cell.HasFormula = True Then
cell.Value = cell / 1000
End If
Next cell
End Sub

 

[excelsishya]

Hi pgc01,

Thank you very much for your code . I was able incorporate u line of code it works perfectly.That is exactly what i needed.

Sub divder()
For Each cell In Selection
If Not cell.Formula Like "*[a-zA-Z]*" = True Then
cell.Value = cell / 1000
End If
Next cell
End Sub

 

[pgc01]

I'm glad it helped!

Remarks:

1 - The Like operator already returns a boolean and so the comparison to True is redundant:

    If Not cell.Formula Like "*[a-zA-Z]*" Then

2- You forgot to declare the variable cell. That is bad practice, and many members in the board (myself included) will not answer to questions with code that has undeclared variables.

You should have "Option Explicit" in the first line of every module. This forces you to declare all variables.

In the code editor you can go to Tools->Options->Editor and check the "Require variable declaration" checkbox. This will include the "Option Explicit" automatically in every new module you insert.

Sub divder()
Dim cell As Range
 
For Each cell In Selection
    If Not cell.Formula Like "*[a-zA-Z]*" Then
        cell.Value = cell.Value / 1000
    End If
Next cell
End Sub

 

[excelsishya]

Hi pgc01 Thanks for Correcting.I will follow in future to declare the variable cell .

This weekend when i was using this code in work place.i came across different variant of this problem. i am posting it in same post as it related to same topic.Please advise should i post it in new thread in future if i get variant of same problem?

The problem i faced is

A1 =5000+B1

Cell had half hard coded, half cell reference .So existing Code will not be effective on this kind of cell.Is it possible in VBA to divide only number (constant) portion of cell ie only 5000 in above example and leaving formula portion.

Thanks

 

[pgc01]

Hi

What is the expected result in the case you posted? Is it:

=5+B1

This type of case is not considered in your original post.

If you need to deal with this type of formulas, please describe rigorously all the possible cases and the expected results.

Ex. Can there be cases like

=4000+A3+2000
=Sum(A2-5000)*1000

Please try to explain it in such a way that covers all the possibilities with examples of inputs and expected results.

 

[excelsishya]

Hi pgc01

Apologies for not posting all possibilities.i came to know second type only last week when i was using original code in office.

The data book contains detailed financial statements which is integrated.I need code which can divide selected cells by thousand only leaving complete formula.

A1=5000
A2=10000
A3=5000+5000
A4=5000+A1(Just for Eg , original will have link from some other tab which would have already divided by 1000)
A5=A1+10000
A6=SUM(A1:A5)

Result A6=5+10+10+5+A1+A1+10

"4000+A3+2000" This kind i still didn't came across.
But if code can take care this possibility too ,it would be great as code will be now all weather number divider.

Thanks for the help.