ExcelBanter - View Single Post

baconcow

I am having issues with some Visual Basic code I am trying to make. My issue
is that I am used to coding with C and other languages (long ago) and I am
not sure where I am going wrong here. I pretty much only have this discussion
group and various Microsoft documentation at my disposal which has been
helpful.

Purpose:
- The purpose of my program is to look at a "Begin Date" which is inserted
into cell "I3" of the worksheet "1.3 - AN - Total Consumption" and then
beginning to compare that Date with the dates listed in Column A of the
worksheet "1.2 - AN Amounts".
- Once the proper ROW is found in which that date occurs, I want to begin
transferring specific cell data, row by row, from worksheet "1.2 - AN
Amounts" to worksheet "1.3 - AN - Total Consumption".
- I want this to occur until I reach an End date as specified in cell "J3"
of the worksheet "1.3 - AN - Total Consumption".

Example of Cell Update (from "1.2 - AN Amounts" to "1.3 - AN - Total
Consumption"):

A10 - A3
B10 - B3
H10 + I10 - C3
E10 - E3
G10 - G3

Then, for the next loop:

A11 - A4
B11 - B4
H11 + I10 - C4
E11 - E4
G11 - G4

etc...

Here is my code:

Code:

Private Sub update_data_Click()

' setup variables
Dim range1 As Range, range2 As Range, range3 As Range, range4 As Range ' 
relative ranges
Dim current_date As Date, current_end_date As Date ' date variables
Dim continue As Integer ' integer variable
range1 = Worksheets("1.2 - AN Amounts").Range("A3")
current_date = Worksheets("1.3 - AN - Total Consumption").Range("I3")
current_end_date = current_date + 6
continue = 1

' find location of current_date in "1.2 - AN Amounts"
While continue = 1
    range2 = range2.Offset(1, 0) ' (RowOffset, ColumnOffset)
    If range2 = current_date Then
        continue = 0
    End If
Wend

' reset continue counter
continue = 1

' offset ranges
range3 = range2.Offset(-1, 3)
range4 = range2.Offset(-1, 5)
Worksheets("1.3 - AN - Total Consumption").Range("D2") = range3 + range4 
'total AN remaining

' place all information from begin week to end week
While continue = 1

' checks to see whether the end week is reached
    If Range("J3") = current_date Then
        continue = 0
    End If

' cell update
    range1 = current_date ' Start Date
    range1 = range2.Offset(0, 1) ' End Date
    range1 = range2.Offset(0, 7) + range2.Offset(0, 8) ' Weekly AN Taken
    range1 = range2.Offset(0, 4) ' Recieved LD AN
    range1 = range2.Offset(0, 5) ' Recieved HD AN
    
' update value for current beginning and end dates
    current_end_date = current_date + 6
    current_date = current_date + 7
    range1 = range1.Offset(1, 0)
    range2 = range2.Offset(1, -5)

Wend

End Sub