Adding the $ AFTER the data was collated?

P

Petitboeuf

Hi all

I have a spreadsheet which links to loads of others.

As all spreadsheets are the same format it was fine for the first lin
to be 'autofilled'. Now as i want to copy down the data it obvisoul
changes the cell references...

Is there any way - short of doing it manually! - of inserting the $ fo
letter and number automatically?

Many thanks
 
S

SamuelT

Hi there,

Not sure I'm totally understanding your problem - but it seems you'r
saying you want to make the cell values absolute after you've entere
them. If this is the case, you can use the following bit of VBA t
'lock' everything up after you've entered it. Just select the cells an
run the macro.

Sub SetAllToAbsolute()
Dim c As Range
For Each cell In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

HTH,

Samuel
 
M

MartinW

Hi Petitboeuf,

There is a semi automatic way.
Click on the cell reference in the formula bar and hit F4,
keep hitting F4 and you will see that it cycles through
the four options for a cell reference i.e.
B2, $B2, B$2 and $B$2

If you want more automated than that then I suppose
there may be some sort of VBA solution that could be
tailored to your particular needs but you would need
to supply a lot more detailed information on your needs
and post it in the programming newsgroup.

HTH
Martin
 
P

Petitboeuf

SamuelT said:
Hi there,

Not sure I'm totally understanding your problem - but it seems you'r
saying you want to make the cell values absolute after you've entere
them. If this is the case, you can use the following bit of VBA t
'lock' everything up after you've entered it. Just select the cells an
run the macro.

Sub SetAllToAbsolute()
Dim c As Range
For Each cell In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

HTH,

SamuelT
Click to expand...

SamuelT,

Many thanks! It worked a treat! :)

Now for the next challenge! :rolleyes:

My formula is something like "=Sheet1!a9/Sheet1!a9" which will give m
a % (100% in that case).
I want to copy/paste down the formula and in order to do so would nee
the formula to be:
"=Sheet1!a9/Sheet1!$a$9"

What would be the tweak on the code to make this happen? I tried t
play with it but my VB abilities do not allow for any results ;)

Many thank
 
P

Petitboeuf

SamuelT said:
Hi there,

Not sure I'm totally understanding your problem - but it seems you're
saying you want to make the cell values absolute after you've entered
them. If this is the case, you can use the following bit of VBA to
'lock' everything up after you've entered it. Just select the cells and
run the macro.

Sub SetAllToAbsolute()
Dim c As Range
For Each c In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

HTH,

SamuelT
Click to expand...

SamuelT,

Many thanks! It worked a treat! :)

Now for the next challenge! :rolleyes:

The formula(s) I am working on are as follow:

=IF(ISERROR('D:\[Dashboards - Accounts - SF2.xls]Dashboard - TOTAL
(Inc. Bridge)'!AJ7/'D:\[Dashboards - Accounts - SF2.xls]Dashboard -
TOTAL (Inc. Bridge)'!B7*100),0,'D:\[Dashboards - Accounts -
SF2.xls]Dashboard - TOTAL (Inc. Bridge)'!AJ7/'D:\[Dashboards - Accounts
- SF2.xls]Dashboard - TOTAL (Inc. Bridge)'!B7*100)

When I try to apply the macro to this.... I get a #Value! error...

Please help! I have a lot of sheets and cells to apply these formulas
to!

Many thanks
 
F

FrankB

Petitboeuf said:
SamuelT,

Many thanks! It worked a treat! :)

Now for the next challenge! :rolleyes:

My formula is something like "=Sheet1!a9/Sheet1!a9" which will give me
a % (100% in that case).
I want to copy/paste down the formula and in order to do so would need
the formula to be:
"=Sheet1!a9/Sheet1!$a$9"

What would be the tweak on the code to make this happen? I tried to
play with it but my VB abilities do not allow for any results ;)

Many thanks
Click to expand...

try this, it worked for me:

Sub SetPartToAbsolute()
Dim c As Range
Dim arSplit As Variant
Dim Numer, Denom As String
For Each c In Selection
If c.HasFormula Then
arSplit = Split(c.Formula, "/")
c.Formula = arSplit(0) & "/" & Application.ConvertFormula(arSplit(1),
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub
 
Top