Using ranges with Visual Basic, stuck...

B

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
 
N

ND Pard

For starters, where is range2?

Your code line:

range2 = range2.Offset(1, 0) ' (RowOffset, ColumnOffset)

You can not reference "range2.Offset..." until you've set a reference to
range2!

baconcow said:
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
Click to expand...
 
B

baconcow

That is my mistake. These are the two correct ranges:

range1 = Worksheets("1.3 - AN - Total Consumption").Range("A3")
range2 = Worksheets("1.2 - AN Amounts").Range("A4")


ND Pard said:
For starters, where is range2?

Your code line:

range2 = range2.Offset(1, 0) ' (RowOffset, ColumnOffset)

You can not reference "range2.Offset..." until you've set a reference to
range2!

baconcow said:
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
Click to expand...
Click to expand...
 
P

Peter T

I am having issues with some Visual Basic code

What sort of Visual Basic, eg .Net, VB/A

If VBA or classic VB you need to use the 'Set' to assign objects, eg
Set range3 = range2.Offset(-1, 3)

you declared "range1 as Range" but what do you expect of this -
range1 = range2.Offset(0, 7) + range2.Offset(0, 8)

to assign the sum of the two cell values ?

Ah I see it must be VBA otherwise you would have done
Dim range1 As Excel.Range

I haven't tried to follow your code.

Add "Option Explicit" without quotes at the top of your module, put the
cursor in the routine and step through pressing F8

Regards,
Peter T


baconcow said:
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
Click to expand...
 
B

baconcow

It is whatever Visual Basic is built into Office 2007.

Microsoft Visual Basic 6.5
VBA: Retail 6.5
 
P

Peter T

OK it's Visual Basic for Applications, VBA.

Refer to the rest of my post.

Regards,
Peter T
 
D

Dave Peterson

First, when you're working with objects (like ranges, workbook, pictures,
worksheets, ...), you have to use Set:

Set range1 = Worksheets("1.2 - AN Amounts").Range("A3")

If you use:
range1 = Worksheets("1.2 - AN Amounts").Range("A3")

it's the equivalent
to
range1.value = Worksheets("1.2 - AN Amounts").Range("A3").value
(and range1 has to be set to a range before that happens).

I have no idea if this does what you want, but it compiles ok:

Option Explicit
Private Sub update_data_Click()

' setup variables
Dim range1 As Range
Dim range2 As Range
Dim range3 As Range
Dim range4 As Range

Dim current_date As Date
Dim current_end_date As Date
Dim continue As Long

Set range1 = Worksheets("1.2 - AN Amounts").Range("A3")
current_date = Worksheets("1.3 - AN - Total Consumption").Range("I3").Value
current_end_date = current_date + 6
continue = 1

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

' reset continue counter
continue = 1

' offset ranges
Set range3 = range2.Offset(-1, 3)
Set range4 = range2.Offset(-1, 5)

Worksheets("1.3 - AN - Total Consumption").Range("D2").Value _
= range3.Value + range4.Value
'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").Value = current_date Then
continue = 0
End If

' cell update
range1.Value = current_date ' Start Date
Set range1 = range2.Offset(0, 1) ' End Date
range1.Value = range2.Offset(0, 7).Value + range2.Offset(0, 8).Value
Set range1 = range2.Offset(0, 4) ' Recieved LD AN
Set 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
Set range1 = range1.Offset(1, 0)
Set range2 = range2.Offset(1, -5)

Wend

End Sub
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Storing multiple ranges using a macro 3
How to track formatting changes 10
Insert range of cells with a (UDF) function 2
Creating a loop 1
Checking for named ranges in a workbook 2
Storing multiple ranges using a macro 1
Improving Workbook_SheetSelectionChange for enhanced Autofiltering 5
I'm trying to do a "count" function at the end of a dynamic pivot table 0

Top