SDesai
07-08-2008, 07:19 PM
I am trying to regenerate an invoice based on the data in an excel sheet
I have managed to get this far-selected the first cell (e.g A10)on the "db" sheet where the invoice number is and copied into the Invoice No in the "OverdueInv" of the same workbook
However, I don't know how to move the cursor to the next column(e.g B10) on db sheet and copy values to C11:G11 cells of "overdueinv" sheet
and then move cursor to D10 or G10 to copy to different cells of the overdue Inv
Sub RegenInv()
Dim userInput As String
Dim rowFound As Variant
Dim dataColumn As Range
Set dataColumn = ThisWorkbook.Sheets("db").Range("A:A"): Rem adjust
Do
userInput = Application.InputBox(userInput & "Which Invoice do you want to regnerate?", Type:=2)
If userInput = "False" Then Exit Sub: Rem cancel pressed
rowFound = Application.Match(Val(userInput), dataColumn, 0)
If IsError(rowFound) Then userInput = "That record not found." & vbCr
Loop Until Right(userInput, 1) <> vbCr
dataColumn.Parent.Activate
dataColumn.Cells(rowFound, 1).Select
ActiveCell.Copy
Sheets("OverdueInv").Select
Range("I6").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
(the problem starts here........don't know the right way to use the offset)
Range("C11:G11").Select
ActiveCell.FormulaR1C1 = "=OFFSET(db!ActiveCell,0,1)"
End Sub
I have managed to get this far-selected the first cell (e.g A10)on the "db" sheet where the invoice number is and copied into the Invoice No in the "OverdueInv" of the same workbook
However, I don't know how to move the cursor to the next column(e.g B10) on db sheet and copy values to C11:G11 cells of "overdueinv" sheet
and then move cursor to D10 or G10 to copy to different cells of the overdue Inv
Sub RegenInv()
Dim userInput As String
Dim rowFound As Variant
Dim dataColumn As Range
Set dataColumn = ThisWorkbook.Sheets("db").Range("A:A"): Rem adjust
Do
userInput = Application.InputBox(userInput & "Which Invoice do you want to regnerate?", Type:=2)
If userInput = "False" Then Exit Sub: Rem cancel pressed
rowFound = Application.Match(Val(userInput), dataColumn, 0)
If IsError(rowFound) Then userInput = "That record not found." & vbCr
Loop Until Right(userInput, 1) <> vbCr
dataColumn.Parent.Activate
dataColumn.Cells(rowFound, 1).Select
ActiveCell.Copy
Sheets("OverdueInv").Select
Range("I6").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
(the problem starts here........don't know the right way to use the offset)
Range("C11:G11").Select
ActiveCell.FormulaR1C1 = "=OFFSET(db!ActiveCell,0,1)"
End Sub