Prev: Search Option
Next: Bug Report: VBA runtime Access Violation
From: Norbert on 19 May 2010 05:41
Hi,
second try to explain what I'm trying to do here.
For the last couple of years we are entering our Production
Specifications into an Excel spreadsheet (PRODUCTION TICKET.xls).
The last step we take after everything is filled in, is copying the
sheet and saving it (values only) under the quality number (e.g.: 4788.xls).

For certain reasons, we need now to get the data back into the original
spreadsheet (it could be on a separate sheet, but it must be in the
original file: PRODUCTION TICKET.xls).

On sheet PRODUCTION TICKET.xls/Prod.ticket, a consecutive code number is
given (entered manually) to every new specification (cell A16). A makro
makes a copy of that particular sheet (Paste special - values) and that
new spreadsheet then is saved under the number in A16, e.g.: 4788.xls).

I thought, I could have a copy of that original sheet PRODUCTION
TICKET.xls/Prod.ticket (in the same xls-file), with certain formulas,
which bring back the values from e.g.: 4788.xls, by me entering the
number 4788 into cell A16 of that copy of sheet PRODUCTION
TICKET.xls/Prod.ticket.

Is that possible??? Do I have to have formulas or rather VBA code.

The formulae should basically look like e.g.:
=[4788.xls]Prod.ticket!$AD$24, but the [4788.xls] should be linked to
cell A16, because it will change all the time.


Norbert

From: Norbert on 19 May 2010 11:23
Bernie, thanks for trying to help me.
this formula didn't work, I could figure out it was because of the first
apostroph:

="='["& A16& ".xls]Prod.ticket'!$AD$24"

So, I changed it to:

="=["& A16& ".xls]Prod.ticket'!$AD$24"

I also changed the address in which my code nr. is entered:

="=["& 'Planning tickets'!$F$1& ".xls]Prod.ticket'!$AD$24"
which shows me in the cell the following:
=[7488.xls]Prod.ticket'!$AD$24 which sounds alright.

Once I start the makro, it hangs in your loop but not changing anything on the sheet.

If Left(rngC.Formula, 6) = "=""=[""" Then (here I also took out the apostroph!)
rngC.Formula = rngC.Value
rngC.Value = rngC.Value
End If

Can you see something strange?




On 19-05-10 15:29, Bernie Deitrick wrote:
> Norbert,
>
> It would be easy to use a macro to do this, if you write your formulas in
> the required cells in a particular way.
>
> For example, in every cell where you want to pull data, use a formula like
>
> ="='["& A16& ".xls]Prod.ticket'!$AD$24"
>
> This will return a string that looks like a formula, but isn't. With 4788
> in cell A16, your cell will show
>
> ='[4788.xls]Prod.ticket'!$AD$24
>
> (Note the two single quotes that you did not originally include...)
>
> Then run this macro, which finds those formulas, converts the string to a
> real formula, and then converts that formula to a value.
>
> Sub ConvertToFormula()
>
> Dim rngC As Range
> For Each rngC In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
> If Left(rngC.Formula, 6) = "=""='[""" Then
> rngC.Formula = rngC.Value
> rngC.Value = rngC.Value
> End If
> Next rngC
>
> ActiveWorkbook.SaveAs Application.GetSaveAsFilename
>
> End Sub
>
>
> The macro will prompt you to save the file under a different name, since all
> the linking formulas will be destroyed.
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Norbert"<n.jaeger(a)gmx.net> wrote in message
> news:%23w$%230fz9KHA.5716(a)TK2MSFTNGP06.phx.gbl...
>
>> Hi,
>> second try to explain what I'm trying to do here.
>> For the last couple of years we are entering our Production Specifications
>> into an Excel spreadsheet (PRODUCTION TICKET.xls).
>> The last step we take after everything is filled in, is copying the sheet
>> and saving it (values only) under the quality number (e.g.: 4788.xls).
>>
>> For certain reasons, we need now to get the data back into the original
>> spreadsheet (it could be on a separate sheet, but it must be in the
>> original file: PRODUCTION TICKET.xls).
>>
>> On sheet PRODUCTION TICKET.xls/Prod.ticket, a consecutive code number is
>> given (entered manually) to every new specification (cell A16). A makro
>> makes a copy of that particular sheet (Paste special - values) and that
>> new spreadsheet then is saved under the number in A16, e.g.: 4788.xls).
>>
>> I thought, I could have a copy of that original sheet PRODUCTION
>> TICKET.xls/Prod.ticket (in the same xls-file), with certain formulas,
>> which bring back the values from e.g.: 4788.xls, by me entering the number
>> 4788 into cell A16 of that copy of sheet PRODUCTION
>> TICKET.xls/Prod.ticket.
>>
>> Is that possible??? Do I have to have formulas or rather VBA code.
>>
>> The formulae should basically look like e.g.:
>> =[4788.xls]Prod.ticket!$AD$24, but the [4788.xls] should be linked to cell
>> A16, because it will change all the time.
>>
>>
>> Norbert
>>
>>
>
>
 | 
Pages: 1
Prev: Search Option
Next: Bug Report: VBA runtime Access Violation