PDA

View Full Version : Copying from cells on same row selected by user



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

grichey
07-08-2008, 07:54 PM
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?

SDesai
07-08-2008, 08:11 PM
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


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

SDesai
07-08-2008, 09:15 PM
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

grichey
07-09-2008, 05:43 AM
so with
"=OFFSET(db!ActiveCell,0,1)
you are trying to put that formula into each of the cells in sheet'overdueinv'
Range("C11:G11").Select

yea?

figment
07-09-2008, 06:49 AM
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.
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