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]

Sub correctsomedates()
  
    Sheets("BALANCES3").Select
      
    lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      
    For a = lastrow To 12 Step -1
      
        firstDate = Cells(a, 1).Value
        secondDate = Cells(a - 1, 1).Value
      
        If firstDate - 1 <> secondDate Then
            'insert new row
            Range(a - 1 & ":" & a - 1).Select
            Selection.Insert
            Range(a - 1 & ":" & a - 1).Select
            Cells(a - 1, 1).Value = firstDate - 1
            a = a + 1
        End If
      
    Next a


End Sub

[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.

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!

Sub processData( data, mycolumn)
      
    myoutput = ""
    myRow = 1
    For a = 1 To Len(data)
     If Mid(data, a, 1) <> " " Then
              myoutput = myoutput + Mid(data, a, 1)
               Else
             If Right(myoutput, 1) <> "%" Then
                 myRow = myRow + 1
                'adjust for trailing - signs
                If Right(myoutput, 1) = "-" Then myoutput = "-" & Left(myoutput, Len(myoutput) - 1)
                                
                Sheets("Output").Range(myColumn & myRow) = myoutput
             End If
                               myoutput = ""
     End If
Next a
    
End Sub

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:

myVar = cells(1,1).value


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

Re: Alternative to Loops

Thanks,

I will give it a go!

Re: Alternative to Loops

I have attached a sample of data with made up addresses instead of real data but the format is the same.

Thanks!

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:

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

Many thanks!

Re: My function needs to know what cell, row and column it is in?

Many thanks!

Hi,

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

function wibble()


  wibble = ("I am in cell " & X & "or column " & 1 & " and row " & 2)




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