Search:

Type: Posts; User: Earlien

Page 1 of 2 1 2

Search: Search took 0.03 seconds.

  1. Replies
    3
    Views
    3,979

    Re: [VBA Excel] .ClearNotes method

    Did some more research.

    Apparently, Cell Comments were called Cell Notes in Excel 2000 and earlier. So .ClearNotes does nothing more than .ClearComments.
  2. Replies
    3
    Views
    3,979

    Re: [VBA Excel] .ClearNotes method

    Ok, thats kinda helpful. But I know that .ClearNotes deletes more than just these 'sound notes' (whatever they were) because I've tested it and I know it deletes comments too. Does it delete...
  3. Replies
    4
    Views
    1,016

    Re: Timing VB Code

    The code I have used in the past to do this is:


    Private Declare Function GetTickCount Lib "kernel32.dll"() As Long

    Private Sub Time_Code()
    Dim t as 'Long
    t = GetTickCount

    ...
  4. Replies
    3
    Views
    3,979

    [RESOLVED] [VBA Excel] .ClearNotes method

    Hi all,

    I have been looking into various methods for deleting cells or contents of cells and am a little confused by the ClearNotes method. Originally I thought it may have been legacy code and...
  5. Re: [VBA Excel] PivotTable SourceData - Union of Ranges

    Thanks dmaruca.

    I find it odd that a union of ranges is not a valid range. After all, they are valid for things like data sources for charts. I guess I will have to create an edited table...
  6. Re: [VBA Excel] PivotTable SourceData - Union of Ranges

    I've just tested using a filtered range as the data source, but it picks up up the hidden/filtered rows as well. Well, doing it manually anyway - not sure if you can specify only visible rows in the...
  7. Re: [VBA Excel] PivotTable SourceData - Union of Ranges

    Ok, I've discovered that if I only use the data section range, the header is automatically included in the range, so there is no need to use the union of the header and data range. E.g.




    ...
  8. [VBA Excel] PivotTable SourceData - Union of Ranges

    Hi. I am having trouble specifying a range as the data source for a pivot cache. It works fine if I specify a simple range, but fails when I use a union of ranges as a range. I am getting a...
  9. Replies
    4
    Views
    1,116

    Re: Counting rows with autofilter on

    I still have not been able to solve this.

    In addition to the above attempts, I have also tried capturing the error by evaluating the problematic code with functions like IsError and IsNull but VB...
  10. Replies
    4
    Views
    1,116

    Re: Counting rows with autofilter on

    Rather than start a new thread, I am extending this thread as I have the same problem and the solutions suggested above do not work.

    Open a new workbook, throw some values into column 1 of...
  11. Re: [RESOLVED] [VBA Excel] Actual used range - Find function not working as expected

    I'm glad someone else found this useful too :)
  12. Re: [VBA] How to Determine the Last Item in a Collection Object

    Yes, that works. I knew there had to be a simple solution. :)
  13. [RESOLVED] [VBA] How to Determine the Last Item in a Collection Object

    Basically, title says it all. I would have thought this to be a fairly easy thing to find, but I cannot find the answer to this in any documentation, or on any forums.

    This is my first time...
  14. Re: [VBA Excel] Type Mismatch: Array or user-defined type expected

    Thanks LaVolpe and SolnArchRet for the input.

    I don't pretend to understand what actually takes place in the background workings of the program, such as whether the value is actually passed or...
  15. Re: [VBA Excel] Type Mismatch: Array or user-defined type expected

    Thats simply because myarr is not declared so VBA handles non-declared variables by making them variant. Same as:



    Sub Test2()
    i = 10
    End Sub


    In the Locals window, 'i' appears as...
  16. Re: [VBA Excel] Type Mismatch: Array or user-defined type expected

    Going back to one of your comments in post #3...



    May I ask where you got this from? I went back to the Microsoft website which describes the Split function as


    Function Split(
    ByVal...
  17. Re: [VBA Excel] Type Mismatch: Array or user-defined type expected

    I realise there are a few ways to solve the problem, Westconn, as you suggested. However, it is the principle that concerns me. These solutions may work just fine for this problem, but I might find...
  18. Re: [VBA Excel] Type Mismatch: Array or user-defined type expected

    Hi Steve. Sorry, I may have mislead you a little bit as I copied and pasted the code and forgot to remove the variable SC_Array1(). Basically, I'm trying to achieve the same solution without using...
  19. Re: [VBA Excel] Type Mismatch: Array or user-defined type expected

    Well, I also tried using a conversion function to coerce it into a string as follows:



    If Search_Array(Test, CStr(Split("Brisbane,Bundaberg,Cairns,Ipswich,Mackay,Rockhampton,Townsville", ",")))...
  20. Re: [VBA Excel] Type Mismatch: Array or user-defined type expected

    As a follow up thought...

    The only thing I can think of is that the split function isn't returning an array unless it is explicitly assigned to one.

    But it seems like the two scenarios are:
    ...
  21. [RESOLVED] [VBA Excel] Type Mismatch: Array or user-defined type expected

    I'm using a split function to create an array of strings (based on Darren M's suggestion in this thread). I have an array which I 'pre-fill' at the start of the code and later use a search function...
  22. Re: [VBA Excel] Actual used range - Find function not working as expected

    Cool, thanks for the feedback Westconn.

    The main reason I don't use the .end() function is because I often work with data in which rows or columns may only have one or a few values, thereby giving...
  23. Re: [VBA Excel] Actual used range - Find function not working as expected

    Coincidentally, I found another one of your posts Westconn in a similar thread, but your suggestion for finding the First used row seems highly inadequate:



    firstrow = range("a9:f99").row

    ...
  24. Re: [VBA Excel] Actual used range - Find function not working as expected

    Alright, I changed the code for calculating the first row to:



    First_Row = XL_Ws.Cells.Find(what:="*", After:=XL_Ws.Cells(Rows.Count, Columns.Count), SearchDirection:=xlNext,...
  25. [RESOLVED] [VBA Excel] Actual used range - Find function not working as expected

    I've seen many people ask how to find the "actual" used range of a worksheet on other forums, because the method that most people would initially think of will includes cells that have had values...
  26. Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line

    I have, and I will :)
  27. Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line

    Hi Opus. Perhaps the following can allay your confusion :)

    Suppose you want to store the integers 1 to 10 in an array. This can be done easily with a loop right?



    Private Sub Test1()

    ...
  28. Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line

    Well, I tried implementing my idea of declaring a second array as an integer, then assigning the values of the first array to the second, then setting the second array to nothing, effectively freeing...
  29. Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line

    Good advice :) I may try this when I'm done and post the results.
  30. Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line

    Its true, I was originally looking at any sort of array, strings, integers or otherwise. However, I think I may only end up requiring integers in the array, so thought I'd push this split function...
  31. Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line

    Hmm, I tried declaring the array as an integer then removing the "A,B,C" part but it returns type mismatch error. I'm guessing that's because the first expression in the split function must be...
  32. Re: [VBA Excel] Assigning values to array in a single line

    Aha!! That's exactly what I was looking for :) I must look into this split function sometime and find out exactly what its doing (although it seems fairly self-explanatory).

    Thanks Darren, you're...
  33. Re: [VBA Excel] Assigning values to array in a single line

    Ok, I've discovered it works if you declare the array as variant AND as a dynamic array. I doesn't appear to work for fixed-length arrays, no matter what data type you use.

    Is there no better...
  34. Re: [VBA Excel] Assigning values to array in a single line

    Actually, I tried this and I still get the compile error "Can't assign to array."



    Dim Rows_Array(1 To 7) As Variant

    Rows_Array = Array(5, 9, 14, 19, 25, 29, 34)


    Any other ideas?
  35. Re: [VBA Excel] Assigning values to array in a single line

    Well, naturally any good programmer wishes to minimise memory used and the time taken to run the code (i.e. less lines of code) :). Based on your first answer and the lack of other replies, I'm...
  36. Re: [VBA Excel] Assigning values to array in a single line

    Thanks for the reply Opus.

    I can't say I'm satisfied with that answer though because I know it would work declaring the array as a string and entering the values separately. There has to be a more...
  37. [RESOLVED] [VBA Excel] Assigning values to array in a single line

    I haven't worked with data arrays in VBA for a while, but thought that it would be a fairly simple procedure to assign values to a one-dimensional array in a single line of code (as opposed to...
  38. Re: [VBA Word/Excel] Error 462 when running the code twice

    Umm...how do I edit the thread title to mark it as resolved?

    Edit: Nvm, worked it out . You have to use the "Go Advanced" options after editing the post. :)
  39. Re: [VBA Word/Excel] Error 462 when running the code twice

    Sorry for the delayed response - I had other projects to complete before Christmas and have only returned to the office yesterday.

    To anyone reading this thread - implementing si_the_geek's...
  40. Re: [VBA Word/Excel] Error 462 when running the code twice

    Thank you si_the_geek,

    I had no idea you had to specify this inside the range as well!

    It seems kind of superfluous that you would need to specify what sheet the cells are on if the sheet for...
Results 1 to 40 of 43
Page 1 of 2 1 2



Click Here to Expand Forum to Full Width