Motor Racing

May

10

Excel VBA - I’m trying to write text from one column to another. What should I change in the following formula?

Posted in: Programming & Design

adam w asked:


Sub CopyPasteCol()
‘
‘ CopyPasteCol Macro
‘ Macro recorded 19/02/2009 by HPDV6426
‘

‘
Range(”BM15:BM99″).Select
Application.CutCopyMode = False
Selection.Copy

Range(”BR15:BR99″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns(”BR:BR”).EntireColumn.AutoFit
End Sub

Sub testCell4()

Range(”A14″).Select
b = Range(”C1″).Value
c = Range(”C5″).Value
s = Range(”A65536″).End(xlUp).Value
Range(”A65536″).End(xlUp).Select

y = (s - b)
Range(”C1″).Value = b + c
Sheets(5).Range(”A14″).Offset(b, 0).Resize(y, 67).Select

As you may have realized, I’ve detailed two Macros, here - I hope to incorporate what I need from the first into the second!

I’m trying to get part of “BM” Column to copy, (or preferably, write) into the BR column (but I have to be careful here, since I don’t want it to overwrite all of the existing info in the BR column. The range is designed to shrink, allowing me to just copy the information I need . After I get the answer to this I will put it into a loop.

You’ve already been a great help btw!

Regards

Tags: Btw, C Range, Macros

1. 
   Gary E
   May 12th 2009

   I think the following code will show you how to do what you want even if you decide not to used it as is.

   Sub WriteBM2BR()
   Dim iTCol, iSCol, iTRow, iSRow As Long
   iSCol = Range(”BM:BM”).Column
   iTCol = Range(”BR:BR”).Column
   iTRow = UsedRange.Rows(1).Row + UsedRange.Rows.Count
   Do While Cells(iTRow, iTCol) = “” And iTRow > 0
   iTRow = iTRow - 1
   DoEvents
   Loop
   For iSRow = 15 To 99
   If Cells(iSRow, iSCol) “” Then
   Cells(iTRow, iTCol) = Cells(iSRow, iSCol)
   iTRow = iTRow + 1
   End If
   DoEvents
   Next
   End Sub

   It “writes” rather than using copy paste as you indicated preferred.

   It is not necessary to calculate and use iTCol and iSCol if you want to just replace then with the column numbers (65 & 70, I believe.)

   The Do While is to guarantee that the writing begins at the first cell following the last non blank cell in Column BR

   The If is to not write (or advance the target row for) blank cells.

   If you have other routines that remove blank cells, you may not need either of those.

   The minimum code would look like:

   Sub WriteBM2BR()
   Dim iTRow, iSRow As Long
   iTRow = UsedRange.Rows(1).Row + UsedRange.Rows.Count
   For iSRow = 15 To 99
   Cells(iTRow, 70) = Cells(iSRow, 65)
   iTRow = iTRow + 1
   Next
   End Sub

   If my answer indicates that I did not understand what you want, please add to your question or email to explain and I will take another look.