extracting a part of a cell

[daveyc18]

so pretty much every day...there will be an adjustment to, say, cell A1 in a file I open

cell A1 would have =random numbers +adjustment

where the adjustment number which will change daily

I then take the adjustment number and offset in cell A2 by typying

=random numbers - adjustment


(the random numbers are originally hardcoded numbers in both cells)

probably a long shot, but any way to automate this??

 

[Joe4]

I think your question may be a bit too generic, and isn't quite clear to us.
Can you walk us through a few actual examples (with real values)?

 

[daveyc18]

I think your question may be a bit too generic, and isn't quite clear to us.
Can you walk us through a few actual examples (with real values)?

sure

i open a file called ABC ...in cell A1 there, =432432+100, where "100" is the adjusted number (both numbers in the formula change daily)

in my own file called DEF, in cell A1 there, =321321-100, where I'm offsetting according to the adjusted number (the "321321" number in this cell changes daily)

 

[Joe4]

OK. So that formula is what is found in cell A1.
So you want to grab the adjustment number (100) and do what with it?
What does A2 contain, a number or a formula?
Do you want to subtract the adjustment from the value in A2?
Is that all, or do we also need to do things to other cells as well?

 

[daveyc18]

OK. So that formula is what is found in cell A1.
So you want to grab the adjustment number (100) and do what with it?
What does A2 contain, a number or a formula?
Do you want to subtract the adjustment from the value in A2?
Is that all, or do we also need to do things to other cells as well?

precisely...grab the adjustment (100) from ABC and subtract the adjustment in a2.

thats the only adjustment....and the cell A2 is originally NOT a formula...it's a hardcoded number...it becomes a formula after the offset is made

 

[Joe4]

This macro should do that:

Sub AdjustMacro()

    Dim frm As String
    Dim adjArray() As String
     
    frm = Range("A1").Formula
    adjArray = Split(frm, "+")
    
    Range("A2").Formula = "=" & Range("A2").Value & "-" & adjArray(1)
  
End Sub

 

[daveyc18]

This macro should do that:

Sub AdjustMacro()

    Dim frm As String
    Dim adjArray() As String
     
    frm = Range("A1").Formula
    adjArray = Split(frm, "+")
    
    Range("A2").Formula = "=" & Range("A2").Value & "-" & adjArray(1)
  
End Sub

wow..amazing, but slight problem

it seems to be picking up the calculated in number in a2

ok...todays file

in a2 ... =-99,633,721.36+100000000

so a2 shows 366,278.64

in a1, i have hardcoded "99,633,721.36"...i need a1 to have ="99,633,721.36 - 1,000,000"

right now the macro does this

a1 = "366,278.64-1,000,000"

 

[Joe4]

I don't understand. You said this in your last post:

precisely...grab the adjustment (100) from ABC and subtract the adjustment in a2.

thats the only adjustment....and the cell A2 is originally NOT a formula...it's a hardcoded number...it becomes a formula after the offset is made

So, as I understand that, A1 contains the formula including the adjustment, and A2 contains a hard-coded number.
And you want us to subtract the adjustment in A2.

However, this part seems to contradict that:

ok...todays file

in a2 ... =-99,633,721.36+100000000

so a2 shows 366,278.64

in a1, i have hardcoded "99,633,721.36"...i need a1 to have ="99,633,721.36 - 1,000,000"

right now the macro does this

a1 = "366,278.64-1,000,000"

Can you clarify which is correct?

 

[daveyc18]

this is correct

in a1, i have hardcoded "99,633,721.36"...i need a1 to have ="99,633,721.36 - 1,000,000"

 

[Joe4]

So, are you saying that you had it reversed originally, where the adjustment value is actually in A2, and not A1?
If so, then just flip the A1 and A2 references in my code, i.e.

Sub AdjustMacro()

    Dim frm As String
    Dim adjArray() As String
     
    frm = Range("A2").Formula
    adjArray = Split(frm, "+")
    
    Range("A1").Formula = "=" & Range("A1").Value & "-" & adjArray(1)
  
End Sub