-
Copying from cells on same row selected by user
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
[VBA]
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
[/VBA]
-
Welcome to the boards!
First use the vba tags to enclose code. I'm sure a mod will come along and modify your post. It's the little button that says VBA at an angle. You just put your code b/w the tags.
Secondly, are you trying to actually put that offset formula into a cell so you can read it if you click or you just the value that would correspond?
-
I just realised that the active cell after the paste refers to I6 on the overdueinv sheet-so the question is how do I return to the active cell on the db sheet and refer offset to it
[VBA]
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
[/VBA]
-
I am not very familiar with VBA so don't know what you mean by tags?
I am just trying to copy the values from the 'db' sheet into the invoice sheet. Don't really require the formula
-
so with
[VBA]"=OFFSET(db!ActiveCell,0,1)[/VBA]
you are trying to put that formula into each of the cells in sheet'overdueinv'
[VBA]Range("C11:G11").Select[/VBA]
yea?
-
what you have now looks like it can be written as this.
i am assuming you want the value of b10 to be put into all the cells in C11:G11
i have not tested this code.
[vba]Sub RegenInv()
Dim userInput As String
Dim rowFound As Variant
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
Worksheets("db").Range("A" & rowFound).Copy
Worksheets("OverdueInv").Range("I6").PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'i didn't test this part so i am not sure if it will work right.
Worksheets("db").Range("B" & rowFound).Copy
Worksheets("OverdueInv").Range("C11:G11").Paste
End Sub[/vba]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules