Posts by jameskilburn

    [SIZE=12px]Hi,


    I am stuck on this problem and I just cannot get my head around it.


    I have a column (column A) with a list of dates, in date order ascending.


    E.g.


    Column A
    01/02/18
    15/02/18
    31/05/18
    01/07/18



    How can I insert the missing dates using a for-next loop in VBA please - I need to add a new row for each new date as other columns have data in them?


    So, I need to add 02/02/18, 03/02/18, 04/02/18 etc, etc.


    This is the code that I have got, but it gets stuck in a loop and basically doesn't work![/SIZE]




    [SIZE=12px]Thanks![/SIZE]

    Re: Reading Very Old Data


    Thanks!


    That is quite complex code, I am sure that I will be able to adapt it to my needs but I would really appreciate some guidance on how the Regex2 code works (I have not come across the object "VBScript.RegExp" before.

    Re: Reading Very Old Data


    Hi,


    I have added a file with simplified version of the data fetch function and sample data.
    The data in RED is the data that doesn't get converted because there is no space between the value, the negative and the percentage.

    Files

    Hi,


    I have been able to extract data from an ancient system (mainframe and terminal emulation programs) in PDF that runs to over 2,000 pages and over 120,000 lines of data but I get stuck with an anomaly in the output and would really appreciate some help please:

    A set of data looks like this:
    Month: Oct 2015 Nov 2015 Dec 2015 JAN 2016 FEB 2016 MAR 2016
    INVOICES: 1230 4500 1000 2230 2345 3456
    CREDITS: 123 10% 4500-100% 100- 10% 22- 1% 23- 1% 346- 10%


    The VBA code I have written looks for spaces and every time it sees a space it treats the data as a single number and transfers the number to the worksheet in columns, increasing the row number each time.


    The data uses trailing negatives and the code converts these.

    On the CREDITS line there are spaces between the trailing negative and the percentage except in one case 4500-100% the code can’t translate these instances and they don’t always have trailing zeros (for some reason there canbe positive credits).


    I should be getting:
    123
    -4500
    -100
    -22
    -23
    -346

    Instead I am getting:
    123
    -100
    -22
    -23
    -346



    Thanks!

    Hi All,


    To avoid using the worlds longest select case statement is there a way of using a variable name to call a subroutine?


    For example:


    Code
    1. myVar = cells(1,1).value
    2. call myVar()


    I was able to use calculated gosub / goto statements (e.g. gosub age * 20) in ancient versions of BASIC on micros years ago and wondered if there was anything similar for subroutines in VBA?


    Thanks,


    J

    I have two worksheets.


    Sheet one is a customer address listing, imported from an accounts package. The fields are not consistent. There can be several words per cell and several cells per customer (each customer has their own row). There are blank fields within the data (rows and columns).


    Sheet two is a list of towns and cities that I need to identify within the customer name and address listing. The data is categorised so there can be several towns listed on each row, with only one town listed per cell. There are blank fields within the data. There are different numbers of towns per row.


    I have written a VBA loop to compare each cell but it is very slow.


    I understand that using Excel Special Cells is the way to go but I have looked at them and don’t see how it is done.


    Extract from current code:


    Code
    1. For Each cell In rng.Cells 'all cells on sheet 1
    2. For Each town In townrange.Cells 'the range pointing to the list of towns on sheet 2 e.g. a1:p260
    3. If InStr(UCase(cell.Value), UCase(town.Value)) then cells(cell.row,1).value = town.value
    4. Next town
    5. Next cell


    Many thanks!

    Hi,


    I am looking for some VBA that will tell my function what cell it is in, for example:


    Code
    1. function wibble()
    2. wibble = ("I am in cell " & X & "or column " & 1 & " and row " & 2)
    3. End function


    The output would be: 1 am in cell A1 or column 1 and row 1



    On an Excel worksheet the equivalent is "=ROW()" or "=COLUMN()" but there does not appear to be an application.worksheetfunction equivalent.


    Many thanks


    James