Posts by stildawn

  • [SOLVED] How to declare this Array usage?

    Re: How to declare this Array usage?


    I also tried (4), but the intelisense came up saying 0 to 3, and I know when you loop through arrays it starts from Array(0) to Array(3) would be a four slot array. So I assumed (3) would be correct (meaning 4 slots)


    Ahh so I'm not declaring an Array, but declaring a variant.


    Thanks will give that a go now.


    Cheers

  • [SOLVED] Using Array with Select Case

    Re: Using Array with Select Case


    Yeah I get how it works (I think) but don't want to have to change the list of countries in each select case, I'll be using the select case thing through out code, so if I could assign it in a array, so I only need to add countries to the array right at the start and it would automatically carry forward to all the select cases.


    Your InStr idea works great though so I'' probably end up using that.

  • [SOLVED] How to declare this Array usage?

    Hi All


    So I have tried a few things but can't figure this out.


    Basically I have a few "sets" of numbers that relate to certain situations. I was putting these numbers into an array in a procedure in a module like this:


    Code
    TTTimes = Array(24, 48, 24, 24)


    However reworking the code to make it more efficient I have move the assigning of this array out of the module and into a userform (which eventually calls the procedures in the module).


    Different sets are required for different situations, at the moment I have a select case like so:



    So depending on the SerCode a different set of TTTimes is required, these TTTimes are used in the procedure "Exceptions"


    So I need to declare this TTTimes array as a public variable in the module so the values assigned in the userform can be used in the module.


    How do I do this? How do you declare a public variable that you can populate like the above? I have tried the following:


    Public TTTimes(3) As Long
    Public TTTimes() As Long


    Both don't work.


    Any ideas?


    Thanks

  • Runtime Error 16 - Expression too complex

    Hi All


    I have ran into this error of a few different lines of code (although the lines are similar):


    Here is the recent one in full:



    These lines are just subtracting one date/time from the other to eventually get the time (in hours) elapsed between them.


    Any ideas on how to fix? There are lots of date subtractions in the code, but if only seems to error on these ones, not the other ones like "Diff = CDate(D1) - CDate(D2)" which is essentially the same.


    Do I really need to just assign the range date/time to a variable before each subtraction? That's going to add a lot a extra lines for no reason.

  • [SOLVED] Using Array with Select Case

    Re: Using Array with Select Case


    Oh does the InStr function works with Arrays? That's handy didn't know that.


    Was trying to avoid a massive if statement looping through the array but the Instr function solves that as you've demonstrated.


    Cheers

  • [SOLVED] Using Array with Select Case

    Hi All


    So I have a few arrays made up of country codes, each array represents a region.


    I'm hoping to somehow use a select case statement to correctly select the correct region (array) for any given country code.


    So what I'd like to do is have a country code and then using select case look inside each array and if it finds a match then thats the region?


    I don't know if this is possible but something like this made up code (which doesnt work):



    So if Origin was "AU" then it would return Region = "Asia", if it was "DE" then it would return Region = "Europe" etc.

  • Date formats again (sigh) - Forcing Excel to keep the correct date from a string

    Re: Date formats again (sigh) - Forcing Excel to keep the correct date from a string


    Holy Cow.


    It seems to have worked???? Amazing.


    This is interesting cause I just tried this code which I randomly dug out of some of my previous VBA work:


    Code
    Dim rgCell As Range
With Sheets(1)
    .Range("G:G").NumberFormat = "dd/mm/yyyy h:mm"
    For Each Cell In .Range("G:G")
        If IsDate(Cell.Value) Then Cell.Value = CDate(Cell.Value)
    Next Cell
End With


    This also worked, but still uses the ".value"


    Man this is odd.

  • Date formats again (sigh) - Forcing Excel to keep the correct date from a string

    Re: Date formats again (sigh) - Forcing Excel to keep the correct date from a string


    Gah even specifically building the date by defining the exact year month and day it still ends up wrong in the cell.




    Man this is sooooo fustrating, if I do this simple assss code:



    It works perfectly it will come up as 04/07/14 perfectly as the correctly recognized date. Why on earth is any of the codes above not working.

  • Date formats again (sigh) - Forcing Excel to keep the correct date from a string

    Re: Date formats again (sigh) - Forcing Excel to keep the correct date from a string


    Not in that specific example, but I have been doing some testing with the same data just in a another excel file (so I can easily test different attempts):


    And I've tried this:



    As you can see from my notes, Msgbox RngDate & " " & RngTime will look perfect, for example it will make 02/07/14 11:10 look exactly like that, but as soon as I put it in the cell, it swaps around again to the wrong format.

  • Date formats again (sigh) - Forcing Excel to keep the correct date from a string

    Hi All


    This I believe has been brought up time and time again, and its one of the things I hate most about excel.


    Basically, I'm in NZ, and we use DD/MM/YY format, I have this raw report from a computer system, that spits out the following:


    [TABLE="width: 520"]

    [tr]


    [TD="class: xl65, width: 104"]02/07/14 11:10[/TD]
    [TD="class: xl65, width: 104"]03/07/14 01:19[/TD]
    [TD="class: xl65, width: 104"]03/07/14 06:35[/TD]
    [TD="class: xl65, width: 104"]03/07/14 14:08[/TD]
    [TD="class: xl65, width: 104"]04/07/14 13:58[/TD]

    [/tr]


    [/TABLE]


    These at the raw state are text or at least not "recognized" as dates by excel.


    On this I run something like this:



    This basically checks a few header conditions, then tries to convert all the dates in the range to "dd/mm/yy h:mm" and trims the cells (cause the raw report is dirty as and every cell even blank ones have a single space in it).


    This results in the following (if run on the data posted above):


    [TABLE="width: 520"]

    [tr]


    [TD="class: xl66, width: 104, align: right"]07/02/14 11:10[/TD]
    [TD="class: xl66, width: 104, align: right"]07/03/14
    1:19[/TD]
    [TD="class: xl66, width: 104, align: right"]07/03/14
    6:35[/TD]
    [TD="class: xl66, width: 104, align: right"]07/03/14 14:08[/TD]
    [TD="class: xl66, width: 104, align: right"]04/07/14 13:58[/TD]

    [/tr]


    [/TABLE]


    As you can see Excel has royally messed up the data, the first date has gone from the 2nd of July, to the 7th of February, as have the next three dates, but randomly enough the last date is correct as the 4th of July in both unworked and worked data.


    So how would you guru's handle this? What commands in VBA etc can I run to ensure the data is the correct data after running? The original text string data is correct, I just need it to be recognizes as Date/Time so I can run some checks between them (measuring time elapsed between points etc).


    And before you ask, my computers date setting is also the local dd/mm/yy format.


    Thanks in advance.

  • Find first empty cell in Sheet 2 Column A

    Re: Find first empty cell in Sheet 2 Column A


    You can find the lastrow of any column anywhere by using:


    Code
    Lastrow = Thisworkbook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUP).Row


    So you would do that, and then for the paste command use: Sheet2.Range("A" & Lastrow + 1).Value = x