Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by chrishooper

  1. chrishooper

    Using VB variables with Excel cell formulas.

    Got it! Thank you so much for your help, everybody. Special thanks to tmktech for that flash of inspiration. chris
  2. chrishooper

    Using VB variables with Excel cell formulas.

    Right here we go again. Thanks for everybody's help so far, by the way. I've tried the simple INDIRECT function example to replace the simple A1*B1 calculation. However when i delve into what's going on it seems that the output of the CONCATENATE function is given as a text string and is not...
  3. chrishooper

    Using VB variables with Excel cell formulas.

    Right well thanks for your help so far guys, i'm a least at little way towards solving the problem the way i want to. If anyway has any further ideas about doing the job using an excel formula referring to a cell containing the worksheet name please don't hesitate to pen your thoughts...
  4. chrishooper

    Using VB variables with Excel cell formulas.

    i think i really need to find a excel function to do what i need instead of having subroutines to changes the formulas. so far i have had a play around with CONCATENATE but i can't get a function such as INDEX to accept a text string as an argument (unless typing in directly !??! :S) eg. if...
  5. chrishooper

    Using VB variables with Excel cell formulas.

    hold up. INDIRECT returns the VALUE of the referenced cell, not just the reference, which is what i needed. and it seemed like such a breakthrough...i just can't see how it could work. any further suggestions??
  6. chrishooper

    Using VB variables with Excel cell formulas.

    a2 has my sheet name in it
  7. chrishooper

    Using VB variables with Excel cell formulas.

    Oooh so close: INDEX(indirect(A2&"'!D3:K6,1,1")) returns a error "you have entered too few arguments", so i tried various permutations: =INDEX((INDIRECT($A$2)&"!D5:K25"),1,1) =INDEX((INDIRECT($A$2&"!D5:K25"),1,1) with no joy. The index function has...
  8. chrishooper

    Using VB variables with Excel cell formulas.

    No joy there Tranpkp
  9. chrishooper

    Using VB variables with Excel cell formulas.

    Hmmm i'm nearly there i think. I'm still having problems with the syntax for putting the formula into the cell (see **) Dim formula1 As String **formula1 = "=INDEX("" & typeselectst & ""!D5:D25," & attvelrow & ",1)&quot...
  10. chrishooper

    Using VB variables with Excel cell formulas.

    Aye, I thought as much. In that case could you have a quick look at this: Dim range As Variant range = D1: D25 Formula1 = Application.WorksheetFunction.Index(typeselectst, range, attvelrow, 1) Worksheets("sheet16").Activate ActiveSheet.[d10].Select ActiveCell.Formula = Formula1 my...
  11. chrishooper

    Using VB variables with Excel cell formulas.

    each calc within a single copied black box with use the same data sheet. However there will be multiple instances of the black box which must each be able to utilised a different source sheets.
  12. chrishooper

    Using VB variables with Excel cell formulas.

    Right well i will have a block of cells on one sheet which will contain versatile formulas such as the index functions. It will be kind of a black box calculator. This must be copiable to any number of other sheets. At the same time it must retain the ability to access data from a number of...
  13. chrishooper

    Using VB variables with Excel cell formulas.

    sorry yeah, i kind of see how it might work though i'm not sure how to use it at all. do you have to then write the new formula's to excel cells? how is this done?
  14. chrishooper

    Using VB variables with Excel cell formulas.

    To write the formula's to the cells?? I can't do this for reasons that would be too complicated to easily explain. Is there no syntax for calling a string in place of the sheet name portion of the function?
  15. chrishooper

    Using VB variables with Excel cell formulas.

    Hi there, I am trying to create Excel cell formulas that can use variables created during the execution of code in VB. In particular I would like to know if it would be possible to specify the sheet selection portion of the INDEX function based on a VB variable. For example, If my basic INDEX...
See more

Part and Inventory Search

Back
Top