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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  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...
  16. chrishooper

    Creating a Class Module to capture multiple combobox clicks

    I should probably add the original code that I was working on, right? This module and class module does exactly what I want, but with CommandButtons instead of ComboBoxes. I have changed the use of the control name in the code to the Tag property of a ComboBox (since comboboxes do not have...
  17. chrishooper

    Creating a Class Module to capture multiple combobox clicks

    I am trying to adapt some code that i ripped from the web to capture commands from multiple comboboxes in a userform, the ultimate goal being to allow them to utilise the same subroutine. I have two comboboxes and have set the tags to "1" and "2" and am trying to get the...
  18. chrishooper

    Control arrays in VBA

    also, how does one trigger the routine you have included? I must be able to do it when any of the included controls are selected.
  19. chrishooper

    Control arrays in VBA

    Ben will i be able to add an indeterminate number of new controls at runtime and still use this method?
  20. chrishooper

    Control arrays in VBA

    Hi there, I need to create multiple controls utilising the same subroutines in excel. After much web searching and head scratching i see that control arrays have been disabled in VBA. Can anyone help me to devise an alternative strategy to solve my problem? I am a very inexperienced programmer...

Part and Inventory Search

Back
Top