Looping & Writing Through Variable Rows & Columns

[wildmini]

Hi Everyone,

My first post here, but not the last - I've recently (through work) started getting involved in some excel VBA work, I've had some experience with VBA at uni but not much, more so with Fortran. So I understand generally how things work, but am struggling with syntax.

Anyway, what I want to do is create a large scheduling type spreadsheet. In order to achieve this, I need to use a lot of statements that can write dates, numbers, values etc. to different rows and columns. At the moment, what I want to do is to (for example) write today's date in the first cell (A1) and then write the next day's date in the next column (B1) and then the next, and so on, until the whole top row basically has a list of dates. I will then use the same type of pricnciple to write into the spreadsheet numbers and schedule information based on user inputs.

Anyway, my problem is, I'm having trouble writing out a date to each column. Normally, embedded within the loop structure, I'd use the Sheets.Range("A1").Value = whatever, is this the right output method? And what do I need to replace "A1" with to cycle through rows and columns when outputting values?

Additionally, I'm using a variable simply named "counter" and using Do While counter <= EndDate (which will be the last date defined by the user) because I don't know what dates the user will input and therefore don't have a set range to work with. Does this sound like the best way of going about this?

 

[RickXL]

VBA is a bit different to FORTRAN.

In FORTRAN you just need to know the commands and have the imagination. With VBA you also need to know the Object Model. What would have been a variable is now a Property and if you do not understand the hierarchy you will find it difficult to make anything work. Been there, done that and felt the frustration!

For instance, one way of setting a cell value would be:

ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "XXX"

So you need to know that workbooks are above worksheets which are above ranges which are above values etc.
Another problem is that there are always a zillion different ways to do anything.
Another difference is that VBA is quite slow. It is usually much faster to use worksheet functions rather that write VBA. This means that loops are usually quite slow. The examples, below, illustrate this.

I have written two sample macros. One fills row 1 with sequential dates and the other fills row 2 (i.e. offset =1) with the string "XXX". The dates routine completes in under a second on my PC but the other one goes away for some time.

Sub WriteDates()
    With ActiveSheet.Range("A1")
        .Formula = "=Today() + Column() - 1"
        .Resize(1, Columns.Count).FillRight
    End With
End Sub

Sub WriteRow()
    With ActiveSheet.Range("A1")
        For counter = 1 To Columns.Count
            .Offset(1, counter - 1).Value = "XXX"
        Next
    End With
End Sub

 

[JoeMo]

VBA is a bit different to FORTRAN.


I have written two sample macros. One fills row 1 with sequential dates and the other fills row 2 (i.e. offset =1) with the string "XXX". The dates routine completes in under a second on my PC but the other one goes away for some time.

Sub WriteDates()
    With ActiveSheet.Range("A1")
        .Formula = "=Today() + Column() - 1"
        .Resize(1, Columns.Count).FillRight
    End With
End Sub

Sub WriteRow()
    With ActiveSheet.Range("A1")
        For counter = 1 To Columns.Count
            .Offset(1, counter - 1).Value = "XXX"
        Next
    End With
End Sub

Actually loops need not be that slow. In this case by suppressing screen updating, the second macro should execute in reasonable time (less than 3 seconds on my computer with 16,384 column count). But , your point remains intact, it is often faster to use worksheet functions.

Sub WriteRow()
Application.ScreenUpdating = False
    With ActiveSheet.Range("A1")
        For counter = 1 To Columns.Count
            .Offset(1, counter - 1).Value = "XXX"
        Next
    End With
Application.ScreenUpdating = True
End Sub

 

[RickXL]

JoeMo,

Thanks for the tip.

The second macro still took 1 minute and 8 seconds without screen updating and 1 minute 16 with.
The first macro runs within a second.

I need a faster computer!