Search:

Type: Posts; User: offthelip; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.04 seconds.

  1. Re: VLOOKUP vs. INDEX/MATCH on variant array

    You haven't specified then names of either of the sheet which contain the lookuip data or the data to be lookup.
    You haven't specified where you want the answer to go, so I assumed:
    1: the table of...
  2. Re: Replicate SUMPRODUCT in VBA to create UDF which is much shorter to simplify appearance of formulae

    is somethign like this what you are looking for:


    Function ho(r1 As Range, r2 As Range, r3 As Range)
    ho = Application.WorksheetFunction.SumProduct(r1, r2, r3)
    End Function
  3. Replies
    6
    Views
    131

    Re: simple code optimization

    Yes that is it more or less, this is what i think you need:


    Sub test()
    Dim rng As Range


    'load all the input data into a varianmt array
    inarr = Range(Cells(1, 1), Cells(x, 14))
  4. Replies
    6
    Views
    131

    Re: simple code optimization

    another cause of your slow speed is that you are accessing the worksheet about 20 times in every loop ,all but one of them is not needed, if you load all the data into a variant array before starting...
  5. Replies
    9
    Views
    144

    Re: formula/code/vba

    Have a look at this thread for how to install a macro in your personal.xlsb workbook so that you can use it anywhere:...
  6. Replies
    9
    Views
    144

    Re: formula/code/vba

    works fine for me.
    Where have you put the macro? and have you assigned a short cut key to it?
  7. Replies
    9
    Views
    144

    Re: formula/code/vba

    Maybe you didn't understand how to use because it will happily paste between workbooks, all it does is do a Pastevalues using a command key.
    Try selecting a cell on a workbook type Ctrl C ( i.e...
  8. Replies
    9
    Views
    144

    Re: formula/code/vba

    I have written my own macro which I store in my personal.xlsb file and I have allocated a short cut key to it. I use Ctrl Q . It does a "pastevalues" into the active cell:


    Sub Pastevalues()
    '...
  9. Replies
    8
    Views
    196

    Re: VBA Loop every 200 milliseconds

    Try I think you will find it does exactly the same as your code
  10. Re: Macro with multiple tabs / copy and paste

    You haven't specified what or where the name is you are looking for so I assumed it was in cell A1 of the active sheet.


    Sub test()
    Name2Find = Cells(1, 1)
    For i = 1 To...
  11. Replies
    8
    Views
    196

    Re: VBA Loop every 200 milliseconds

    you could speed up the copy by doing all the columsn at once eg:
    replace:

    Worksheets("Main Data").Range("AI5:AI11").Value = Worksheets("Main Data").Range("AH5:AH11").Value
    Worksheets("Main...
  12. Thread: Repeat Macro

    by offthelip
    Replies
    4
    Views
    109

    Re: Repeat Macro

    It might help if you explain a bit more about what you are trying to do, because it should not be necessary to continually recalculate all the time. If you are trying to solve a problem by doing that...
  13. Replies
    12
    Views
    481

    Re: VLookup not working in VBA

    I finally got back to my computer and solved your problem you need to change the declaration of myarray
    to:

    Dim MyArray(1 To 5, 1 To 5) As Variant
  14. Replies
    12
    Views
    481

    Re: VLookup not working in VBA

    Your problem is you defined myarray as type long, msgbox only types out strings, so the assignment to myarray is perfectly ok ( I think) I think the problem is just with your method of looking at it....
  15. Replies
    12
    Views
    481

    Re: VLookup not working in VBA

    I think something like this is what you want:


    Sub test2()




    Dim MyArray(5, 5) As Long 'defines array as being 5 rows, 5 columns
    Dim X As Long
  16. Replies
    1
    Views
    110

    Re: Organisation Chart in Excel

    I would create a "helper" column which defined what level the employees are at, and then write some vBA to run down this column grouping the numbers and when they change start a new group. using the ...
  17. Replies
    12
    Views
    481

    Re: VLookup not working in VBA

    Can you describe the whole problem because it is not clear from your code what you are trying to do. Your code would appear to load the same value (inarr(j,i) into all positions in the Myarray up...
  18. Replies
    12
    Views
    481

    Re: VLookup not working in VBA

    I would never use vlookup in vbA, it is easier and more flexible to do it using varianta rrays and a few loops:
    try this:


    ' load all the data into variant array
    inarr = Range("K1:U10")
    vb2 =...
  19. Replies
    12
    Views
    475

    Re: Problem with VBA Loop

    Which line is this error coming up on. Please help us by giving us all the information about the error, ie. what the error is and what line it is on and if it is subscript out of range , hover over...
  20. Re: Excel (2007) Web Query data -- how to stop annoying script errors in the built-in browser?

    I think I have worked out how to do it.
    I don't use internet explorer as my defualt browser. However I opened internet explorer and then reset the home page to a different page, I thne started a...
  21. Re: Excel (2007) Web Query data -- how to stop annoying script errors in the built-in browser?

    I have managed to change the default page that comes up when I start a web query, however since I did this when Excel 2007 was the latest version over 10 years ago, I can't actually remember what I...
  22. Replies
    12
    Views
    475

    Re: Problem with VBA Loop

    which line do you get the error on?
  23. Replies
    12
    Views
    475

    Re: Problem with VBA Loop

    which line do you get the error on? The "Dim alpha_1y As Variant" line is not actually necessary since excel defaults all variables to variants.
    The "Sub or Function not defined" error was due to my...
  24. Replies
    12
    Views
    475

    Re: Problem with VBA Loop

    You can do the whle calculation in VBA which will be much faster:
    try this
    [CODE]
    Sub segment_trigger_returns()
    Application.ScreenUpdating = False
    Dim segment_trigger As Boolean
    Dim data_set As...
  25. Re: vba to copy one cell value to another cell value

    If A50 is letters and numbers it will default to being a string so EXCEL can't add it to a number. You need to specify what the format of the "number" in A50 is. And then you need to manipulate in a...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4