Input a value into a cell.

[David Langschied]

This, I thought, was going to be easy and have not figured it out.

I am inputing a row of data from one worksheet to another. I want to add a
new column that contains the number of the row just created by the data.
So...

Sub Otros_XXXX_Cuenta()
'Select Transaction Sheet
Sheets("Hoja_de_seleccion_de_trans").Select
'Store the row information to be copied From the transaction worksheet
'at global RowNum
cellrange = "B" + CStr(rownum) + ":" + "P" + CStr(rownum)
'Increment the Otros Row Number
rownumO = rownumO + 1
'Select the row to import into Otros
Range(cellrange).Select
'Setup the selection copy
Application.CutCopyMode = False
Selection.Copy
'Setup the paste
Sheets("Otros_HSBC_Cuenta").Select
Range("A" + CStr(rownumO)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Add new row for row number value
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
'Select the cell to update
Range("A1").Select
'Update cell with row number
Range("A" + CStr(rownum)).Value = rownumO
'Go Back to transaction sheet
Sheets("Hoja_de_seleccion_de_trans").Select
End Sub

I tried the update above, I tried using cell.value and I keep getting
errors. It is probably really simple, but I cannot figure it out.

 

[Laroche J]

David Langschied wrote on 2009-05-11 14:12:

This, I thought, was going to be easy and have not figured it out.

I am inputing a row of data from one worksheet to another. I want to add a
new column that contains the number of the row just created by the data.
So...

Sub Otros_XXXX_Cuenta()
'Select Transaction Sheet
Sheets("Hoja_de_seleccion_de_trans").Select
'Store the row information to be copied From the transaction worksheet
'at global RowNum
cellrange = "B" + CStr(rownum) + ":" + "P" + CStr(rownum)
'Increment the Otros Row Number
rownumO = rownumO + 1
'Select the row to import into Otros
Range(cellrange).Select
'Setup the selection copy
Application.CutCopyMode = False
Selection.Copy
'Setup the paste
Sheets("Otros_HSBC_Cuenta").Select
Range("A" + CStr(rownumO)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Add new row for row number value
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
'Select the cell to update
Range("A1").Select
'Update cell with row number
Range("A" + CStr(rownum)).Value = rownumO
'Go Back to transaction sheet
Sheets("Hoja_de_seleccion_de_trans").Select
End Sub

I tried the update above, I tried using cell.value and I keep getting
errors. It is probably really simple, but I cannot figure it out.

Hi David

I'm not exactly sure about the result you expect. From reading and executing
your code I observe that:
1- You want to copy columns B to P from a row of sheet
Hoja_de_seleccion_de_trans...
2- ... into cells A to O of sheet Otros_HSBC_Cuenta, on row RowNumO. RowNumO
must be a global variable otherwise pasting would always be done in row 1.
3- Then you shift this content one column to the right by inserting a new A
column. Why wouldn't you paste immediately in column B to avoid this shift?
4- Finally in row RowNum of this new column A you enter the row number where
you pasted your data. I'd use RowNumO, not RowNum, otherwise data and row
number won't be on the same row. Do you want to put the number on the same
row where you copied the data (for what purpose?), or in a fixed cell, which
makes more sense to me?

For this to work you also need to externally define the value of RowNum,
which I guess comes from somewhere else as you say it's a global variable.

One major flaw of shifting the columns is that every time you copy a row the
previously copied rows are also moving right, and the longer they have been
on the second sheet the farther right they slide.

Also, your code is extremely slow due to the constant use of Selection.
There are easy ways to optimize execution. I'd also make RowNum an argument
of the Otros_XXXX_Cuenta sub to increase flexibility and portability.

Here's what I propose, it's much faster and I think it solves all these
problems, if I understood well. Correct if I made false assumptions or post
again with precisions.


Sub Otros_XXXX_Cuenta(LocalRowNum)

With Sheets("Hoja_de_seleccion_de_trans")
' Define the range to copy with Set
Set cellrange = Range(.Cells(LocalRowNum, 2), _
.Cells(LocalRowNum, 16))
End With

' Increment the Otros Row Number
RowNumO = RowNumO + 1

With Sheets("Otros_HSBC_Cuenta")
' Copy the range from one sheet to the other
cellrange.Copy (.Cells(RowNumO, 2))
'Update cell with row number (this is still unclear)
.Cells(RowNumO, 1).Value = RowNumO
End With

'Go Back to transaction sheet
Sheets("Hoja_de_seleccion_de_trans").Activate

End Sub


JL
Mac OS X 10.4.11
Office v.X 10.1.9

 

[David Langschied]

Actually, I figured it out. Good old macro recorder! I just recorded what I
was trying to do and looked at the code. Here is the change I made to the
code:

Range("A" + CStr(rownumO)).Select
Selection.Insert Shift:=xlToRight
ActiveCell.Value = CStr(rownumO)

It is the ActiveCell.Value that I was looking for!

Thank you so much for your response. I do very much appreciate you guys out
there with all your knowledge. I am working on a process that has become
quite a bear and it is people out on this site that has gotten me over the
humps.

 

[Laroche J]

David Langschied wrote on 2009-05-12 07:59:

Actually, I figured it out. Good old macro recorder! I just recorded what I
was trying to do and looked at the code. Here is the change I made to the
code:

OK, glad you figured it out. If you plan to make a large program though,
consider these tips to make the execution faster.

1- Macro recording is helpful to learn how to write a difficult sequence,
but should be cleaned up afterwards because it's highly inefficient. For
example,
Range("A" + CStr(rownumO)).Select
Selection.Insert Shift:=xlToRight
can be optimized by
Range("A" + CStr(rownumO)).Insert Shift:=xlToRight

2- Object methods are usually in place to replace many manual operations.
For example,
Range("F24:F28").Select
Selection.Copy
Range("H24").Select
ActiveSheet.Paste
Application.CutCopyMode = False
can be replaced by the fast and elegant
Range("F24:F28").Copy Range("H24")
or across sheets
Sheets(1).Range("F24:F28").Copy Sheets(2).Range("H24")

3- Of course, instead of pasting and shifting like you do, why not paste at
the right spot the first time?
Range("A" + CStr(RowNumO)).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A" + CStr(RowNumO)).Select
Selection.Insert Shift:=xlToRight
ActiveCell.Value = CStr(RowNumO)
should actually simply be
Range("B" + CStr(RowNumO)).PasteSpecial Paste:=xlPasteValues
Range("A" + CStr(RowNumO)).Value = RowNumO
ActiveCell is a dangerous item when you're moving around the sheets. Better
get an absolute reference when possible.

4- Don't forget the object Cells, which simplifies your cell reference.
Range("A" + CStr(RowNumO))
can be replaced by
Cells(RowNumO, 1)
which eliminates the need to convert numbers into strings all the time, and
makes calculated references easier to obtain.

5- If a macro is doing a lot of cell copying, moving or formatting, speed up
the execution by beginning it with
Application.ScreenUpdating = False
and finishing it with
Application.ScreenUpdating = True
Doing so, Excel won't bother fixing the display each time an operation
changes the appearance of the sheet. In a test of looping your macro 325
times, this alone (around the loop) reduced the execution time by a factor
of 25! My macro as posted before reduced it by another factor of 8, for a
total of 200.

Enjoy your coding!

JL
Mac OS X 10.4.11
Office v.X 10.1.9