visual basic
 

I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks in
a year, does someone know how I can repeat the code and have it change cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any help

KMF

visual basic
 

For i = 1 to 52
If Target.Address = "AP" & i + 3 Then
Worksheets("$INV1").Range("$A$" & i + 21).Value = _
Target.Value
End If
Next i

Sharad

"kmf" wrote in message
...
I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As
Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks
in
a year, does someone know how I can repeat the code and have it change
cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any
help

KMF

visual basic
 

Untested air code:

Private Sub Worksheet_Change(ByVal Target As Range)

' (Note: Column AP = Column 42)
If Target.Column = 42 Then
Select Case Target.Row
Case 4 to 56
Worksheets("$INV1").Range("$A$" & Target.Row + 18).Value =
Target.Value
Case Else
' Do nothing if target row is less than 4 or more than 56
End Select
End If

Since you say that you have been using code similar to this, I assume
you realize that you are copying the value of the cell (Target) you are
moving TO, not the value of the cell you just LEFT.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"kmf" wrote in message
...
I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As
Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks
in
a year, does someone know how I can repeat the code and have it change
cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any
help

KMF

visual basic
 

thankyou sharad,

I am new to vis basic and I rewrote below what I am trying to accomplish. I
did not understand what you wrote and wonder if could explain further. I
rewrote below what I am trying to do.
I am using excel spreadsheets to track sales in a business. I enter sales
data each week onto a spreadsheet and was given the formula below to use so
it would
automatically be entered onto a invoice I created on a separate excel sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
I want to copy this so it works in in multiple columns that correspond to
past or future weeks. I will have to repeat this many times because of
different time periods and different customers . In other words if I enter
sales data for several products this week it would go to the invoice and I
could print it. I want to keep the data I entered foe historical reasons and
want to enter next weeks data in a new column for that week, but it would
still go to the same invoice form since after I print it the values for the
invoice form are cleared. So as i enter sales data each week in each new
column I would like it to go to the same invoice form. Can i write a code or
copy a vis basic formula that will take into account the new columns
automatically so i do not have to code every cell, just as excel works when
you copy from cell to cell it adjusts the cell address. thanks for any help.




"Sharad Naik" wrote:

For i = 1 to 52
If Target.Address = "AP" & i + 3 Then
Worksheets("$INV1").Range("$A$" & i + 21).Value = _
Target.Value
End If
Next i

Sharad

"kmf" wrote in message
...
I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As
Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks
in
a year, does someone know how I can repeat the code and have it change
cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any
help

KMF