Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.05 seconds.

  1. Re: For loop with irregular step or use arrays if possible

    I don't quite understand why you printout sheet2 for every mathc but this code does same as your code ( I think I haven't tested it!!). It uses variant array to do the vlookup which is much much...
  2. Replies
    18
    Views
    543

    Re: How to speedup VBA

    I am pleased to hear that it is now much faster.
  3. Replies
    18
    Views
    543

    Re: How to speedup VBA

    put this line in after the loop start:


    For j = 8 To 14 Step 1
    if inarr(j,1)="" then exit for
  4. Re: For loop with irregular step or use arrays if possible

    I have annotated the code to help you:


    Sub test()
    inarr = Range("A1:B100")
    Dim outarr(1 To 100, 1 To 2) 'This defines an output variant array with dimensions 100 by 2, this is equivalient to...
  5. Replies
    5
    Views
    150

    Re: Loop and Long Formula

    When I am trying to debug writing equations with VBa, I write the equation as text by leaving out the "=" sign, then go to the cell and insert the "=" and excel will very kindly show you where the...
  6. Re: For loop with irregular step or use arrays if possible

    you haven't given much detail about the restof your worksheet but something like this might help you:
    I have loaded columns A and B into a varaint array and loop through the inut array and if the...
  7. Replies
    18
    Views
    543

    Re: How to speedup VBA

    put a test is to check if the data is blank
  8. Re: Excel lags when switching between automatic & Manual calculations

    Have you checked your personal.xlsb file to see whether that is causing the problem, or any other workbook that starts up automatically.
  9. Re: Optimizing code to copy data from one workbook and paste in another

    it is much more efficient to load all of the data from your active sheet into a variant array asnd then write it back to the "fees" worksheet in one go
    this code does exactly what your code above...
  10. Replies
    18
    Views
    543

    Re: How to speedup VBA

    try this:


    Sub test()
    Dim outarr(1 To 7, 1 To 9) As Variant




    With Worksheets("sheet1")
  11. Replies
    18
    Views
    543

    Re: How to speedup VBA

    it is not entirely clear which which cells your variables are sitting in and I don't know which sheets are the source and destination, so I am sure this is not correct but it should show you how to...
  12. Replies
    9
    Views
    190

    Re: How to Re-Write a Very Long Formula

    what about trying a formual like this:

    =iferror(MATCH($J156,AC156:AT156,0),MATCH($J156,AC156:AT156,1)+1)
  13. Replies
    17
    Views
    204

    Re: adding time issue

    have you tried :
    =Timevalue(A1) + Timevalue(A2)
  14. Replies
    5
    Views
    54

    Re: =if(condition,,)

    if is because "If" statement has the form =IF( test condition, value if test condition is TRUE, Value if Test condition is false)
    You have got NOTHING in both the true case and the false case so it...
  15. Re: Delete all sheets that meets my critera without prompt

    I think that is because as the worksheets get deleted the worksheet count goes down so the loop goes too far, so we need to put acheck in at the end of the loop,
    try this:

    Dim nm As String

    Dim...
  16. Re: Delete all sheets that meets my critera without prompt

    try this code:


    Dim nm As String
    Dim Namesar(1 To 3) As String
    Namesar(1) = "PrixMax"
    Namesar(2) = "6po"
    Namesar(3) = "Quote"
    For i = 1 To Worksheets.Count
    nm = Worksheets(i).Name
  17. Re: Not allow users to hide columns in protected sheet

    I had a further thought : you will also need to put this code in the workbook before save event, to prevent them savign the workbook with columns hidden:


    Private Sub Workbook_BeforeSave(ByVal...
  18. Re: Not allow users to hide columns in protected sheet

    you could allow the users to hide the columns but unhide tehm when they deactivate the sheet by putting this in the worksheet deactivate event. ( I like the sneaky way this works!!)


    Private Sub...
  19. Re: Formula to Find matching Absolute Values in a Range of Dates?

    You could divide the date by 10 and round it down and then multiply it back up again. This will make all the dates in a 10 day period identical: e.g.

    =10*ROUNDDOWN(NOW()/10,0)
  20. Replies
    6
    Views
    115

    Re: Hide columns that have whole rows TRUE

    if there only 3 possibliities as you specified you can change the test to:

    if (inarr(i,col)="False") then
  21. Replies
    6
    Views
    115

    Re: Hide columns that have whole rows TRUE

    that is because Inarr(i,col) was assumed to be a boolean, it would appear that sometimes there is something in the cell that is not TRUE or FALSE, so I suggest trying changing it to:

    If Not...
  22. Re: Excel: Dynamic plot with specific variable on x-axis

    No, not quite, this will do it:

    cells((lastrow+1,2)=cells(2,3)
  23. Replies
    15
    Views
    400

    Re: Combine matches

    looks like an ideal example where using a dictionary of dictionaries would solve your problem.
  24. Replies
    18
    Views
    543

    Re: How to speedup VBA

    I have modified your code to show you how to do it partly with variant arrays. I don't have time to completely rewrite. this is still writing to individual cells on every loops but it is now not...
  25. Replies
    18
    Views
    543

    Re: How to speedup VBA

    Can you post all of your code, please
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4