| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ranges with Visual Basic, stuck...
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
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using ranges with Visual Basic, stuck...
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" wrote: 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
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using ranges with Visual Basic, stuck...
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" wrote: 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" wrote: 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
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using ranges with Visual Basic, stuck...
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" wrote in message ... 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
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using ranges with Visual Basic, stuck...
It is whatever Visual Basic is built into Office 2007.
Microsoft Visual Basic 6.5 VBA: Retail 6.5 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using ranges with Visual Basic, stuck...
OK it's Visual Basic for Applications, VBA.
Refer to the rest of my post. Regards, Peter T "baconcow" wrote in message ... It is whatever Visual Basic is built into Office 2007. Microsoft Visual Basic 6.5 VBA: Retail 6.5 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using ranges with Visual Basic, stuck...
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 baconcow wrote: 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
-- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
| Programmatic access to visual basic project via Visual FoxPro | Excel Programming | |||
| Make visual basic truely visual! | Excel Programming | |||
| Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming | |||
| changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Hybrid Mode
