Getting rid of the $ signs in VBA

[Dolemitefunk]

Hello again everyone, I was wondering if there was a way to unlock the Cells and Columns in VBA. So in otherwords, if I link a cell to another workbook, it automatically has $'s around the cel (eg $C$31), is there code in VBA to remove those signs? I have tried the record function, but it did not help. Thanks Again to all of you that are keeping us frustrated folks sane.

 

[pgc01]

... So in otherwords, if I link a cell to another workbook, it automatically has $'s around the cel (eg $C$31), is there code in VBA to remove those signs?

Hi

How are you doint this? Please post the code you are using to do this.

 

[Norie]

Can't you just delete them yourself?

If you are using the macro recorder then you need to understand it isn't going to produce the exact code you need.

It's inevitable that you'll need to eke some changes.

Use it to get the general syntax that you need, not the complete code.

 

[Ron Coderre]

Perhaps a bit of prevention:
After selecting the cell/range in the external workbook
and before pressing enter...
Each time you press the [F4] key the reference will cycle through
the absolute/relative options.

Example:
When the formula bar initially displays:
='[MyOther Workbook]Mysheet'!$A$1
Press [F4]...formula becomes: ='[MyOther Workbook]Mysheet'!A$1
Press [F4]...formula becomes: ='[MyOther Workbook]Mysheet'!$A1
Press [F4]...formula becomes: ='[MyOther Workbook]Mysheet'!A1
Press [F4]...formula becomes: ='[MyOther Workbook]Mysheet'!$A$1

Does that help?

 

[Dolemitefunk]

Sub Testing2()
Dim master As Workbook
Dim factors As Worksheet

Set master = ActiveWorkbook
Set factors = master.ActiveSheet

Set Large= Workbooks.Open("U:\ME\Quantity.xls")

Set Numbers= Large.Worksheets("Color BK")
Numbers.Select
Range("H14").Select
ActiveCell.FormulaR1C1 = "='[Comic Tables.xlsm]Sheet1'!R31C6"
Range("H14").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

 

[Dolemitefunk]

I tried recording it while hitting F4 to remove the dollar signs, but it didnt do anything to the code.

 

[Ron Coderre]

My apologies for the confusion...
I was thinking you wouldn't need to use VBA to correct the references
after-the-fact if you could easily create relative references just by
pressing [F4] when creating them.

 

[Dolemitefunk]

This is true, But this code is going to be applied to 50 different workbooks, and to 10 pages per workbook,

 

[Norie]

Eh, am I missing something here?

The reference you mention isn't in that code.

Are you actually referring to the R1C1 reference in the code?

If you are,I replace FormulaR1C1 with Formula and replace the reference with the equivalent A1 reference.

 

[Dolemitefunk]

Eh, am I missing something here?

The reference you mention isn't in that code.

Are you actually referring to the R1C1 reference in the code?

If you are,I replace FormulaR1C1 with Formula and replace the reference with the equivalent A1 reference.

So ActiveCell.Formula = "='[Comic Tables.xlsm]Sheet1'!R31C6"?