[Belluaarmis]

Hello all,

First and foremost I will begin by stating I have gone through more tutorials than I care to remember thus far. With that being said here is my dilema, I hope to create a macro that will change the value of a cell, date, and then print 7 times. Here is my code using the VBA editor:

Sub day_change_print()
Dim i As Integer
Dim current_date As Integer

i = 1

current_date = range(a1).value
Do
current_date = current_date + 1
ActiveWorkbook.PrintOut
i = i + 1
range(a1) = current_date
Loop Until i = 7

End Sub


My problem is line:

current_date = range(a1).value

In debugging I have noticed range(a1) value is empty. Which makes sense, I guess, being I did not pass the value as an argument. So I tried:

Sub day_change_print(ByRef current_date as Integer)

which will not even run.

Next I thought I would give this a try:

current_date = today()
Do
= current_date + 1
ActiveWorkbook.PrintOut
i = i + 1
range(a1) = current_date

Loop Until i = 7

I am just spinning my wheels here so I thought I would break away...clear my mind... swallow my pride... and ask for some advice. So how does one assign a value to a variable form an existing cell's value? Or even better how does one acess the today() function from within a sub routine?

Any thoughts on these topics are greatly appreciated.

 

[Belluaarmis]

I figured a way out incase anyone else has this problem:


' This macro is designed to print 7 iterations of a workbook
' Where the date is incrimented by one each time the worbook is printed
' programmed by
Sub day_change_print()

' Declarations
Dim i As Integer
Dim current_date As Date
' Assignments
i = 1
current_date = DateTime.Date
' Functional aspect
Do
Range("a1") = current_date
ActiveWorkbook.PrintOut
current_date = current_date + 1
i = i + 1
Loop Until i = 3

End Sub

 

[Jimmy the Hand]

Hi Belluaarmis,

Though you have figured it out allow me to make some comments.
1. If you know, in advance, that the loop will be executed 7 times, you can use For... Next loop instead of Do... Loop. It's simpler to code and read.

2. You can do math operations on cell values without the need to read them into variables.
So, you can say Range("A1").Value = Range("A1").Value + 1

3. The real problem in Post#1 was the reference to cell A1.
Range("A1") is perfect. (Note the quotation marks.)
Range(A1) is not, because A1 is a variable that hasn't been defined.

4. There is a CODE tag in the post editor window, it looks like #. By using it, the VBA code will be more readable to forum users.

To sum up:

Sub day_change_print()
' Declarations
   Dim i As Integer

' Assignments
   Range("A1").Value = Date() - 1

' Functional aspect
   For i = 1 to 7
      Range("A1") = Range("A1").Value + 1
     'An equivalent:
     'Range("A1")  = Date() + i
      ActiveWorkbook.PrintOut
   Next i
End Sub

 

[Zack Barresse]

I would only change one thing Jimmy...

 Dim i As Integer

.. to ..

 Dim i As Long

 

[Jimmy the Hand]

... ?

... ?

Yes, I remember now. Once (ore twice) you've said here that compiler transforms integers to long, anyway, so there's no point in using them. (Unless my PC is too fast and I want to slow it down )

I'll try not to forget this anymore.

Thanks,
Jimmy

PS:
I miss those smilies from VBAX... I'm getting used to them

 

[Zack Barresse]

Merry Christmas Jimmy!

 

[Belluaarmis]

Thanks alot guys for all the information.

Bell

 

[Bob in Wessex]

Yes, I remember now. Once (ore twice) you've said here that compiler transforms integers to long, anyway, so there's no point in using them. (Unless my PC is too fast and I want to slow it down )

I'll try not to forget this anymore.

No, the OS converts an integer to a long, works on it (and converts it back to integer if returning it).