On Jul 28, 12:26*pm, RadarEye <wouter.ma...@sogeti.nl> wrote:
> On 28 jul, 20:35, ucanalw...@gmail.com wrote:
>
>
>
>
>
> > Hello Group,
>
> > I am getting cell reference error when I try to do the following. This
> > is what I have:
>
> > I have an excel sheet with bunch of data. All I am trying to do is to
> > select the set of cells that have the same B range value and paste it
> > into a new workbook and save it as a *.txt file.
>
> > For example,
> > B2 to B102 has the same value 101.23; cells C2 to Z102 have different
> > values
> > B103 to B176 has the same value 255478.32; cells C103 to Z176 have
> > different values
> > B177 to B250 has the same value 2412.56; cells C177 to Z250 have
> > different values
> > .....
> > B40213 - B40315 has 122453.2; cells C40213 to Z40315 have different
> > values
>
> > I am trying to
> > Copy vaules B2 to Z102, paste it in a new workbook and save it as a
> > B2value.txt file.
> > Copy vaules B103 to Z176, paste it in a new workbook and save it as a
> > B103value.txt file.
> > .....
> > Copy vaules B40213 to Z40315 , paste it in a new workbook and save it
> > as a B40213.txt file.
>
> > I have the macro to paste the selected data into a new workbook and
> > save it as a txt file but I am getting errors when I try to copy the
> > set of cells that have the same value in cell B.
>
> > Please let me know the macro condition required to accomplish this.
>
> > I am going to keep on tweaking my existing code but I would really
> > appreciate any help.
>
> > Thanks!
> > Kevin
>
> Hi Kevin,
>
> I have cooked this macro in excel 2003:
>
> ' ---------- START
> Private Const constLastColumn As Integer = 26
> Private Const constSeparator *As String = "|"
>
> Public Sub SplitOnValueOfColumnB()
> * * Dim lngCurrentRow * * * As Long
> * * Dim lngValueOfColumnB * As Long
> * * Dim rngSingleValue * * *As Range
> * * Dim intFreeFile * * * * As Integer
> * * Dim strSaveAsFolder * * As String
> * * Dim strSaveAsFile * * * As String
> * * Dim intLastSlash * * * *As Integer
> * * Dim strPrintLine * * * *As String
>
> * * strSaveAsFolder = Application.GetSaveAsFilename( _
> * * * * InitialFileName:="B2Value.txt", _
> * * * * FileFilter:="Text (*.txt),*.txt", _
> * * * * Title:="Select folder for first file")
> * * If strSaveAsFolder <> "False" Then
> * * * * intLastSlash = InStrRev(strSaveAsFolder, "\")
> * * * * strSaveAsFolder = Left(strSaveAsFolder, intLastSlash)
> * * * * lngCurrentRow = 2
> * * * * Do While Not IsEmpty(Cells(lngCurrentRow, 2))
> * * * * * * lngValueOfColumnB = Cells(lngCurrentRow, 2)
> * * * * * * intFreeFile = FreeFile
>
> * * * * * * strSaveAsFile = strSaveAsFolder & "B" & _
> * * * * * * * * CStr(lngCurrentRow) & "value.txt"
>
> * * * * * * Open strSaveAsFile For Output As intFreeFile
>
> * * * * * * Do While lngValueOfColumnB = Cells(lngCurrentRow, 2)
> * * * * * * * * strPrintLine = ""
> * * * * * * * * For Each rngSingleValue In _
> * * * * * * * * * * Range(Cells(lngCurrentRow, 2), _
> * * * * * * * * * * * * * Cells(lngCurrentRow, constLastColumn))
> * * * * * * * * * * strPrintLine = strPrintLine & _
> * * * * * * * * * * * * * * CStr(rngSingleValue.Value) & _
> * * * * * * * * * * * * * * constSeparator
> * * * * * * * * Next
> * * * * * * * * Print #intFreeFile, strPrintLine
> * * * * * * * * lngCurrentRow = lngCurrentRow + 1
> * * * * * * Loop
> * * * * * * Close intFreeFile
> * * * * Loop
> * * * * Cells(1, 1).Select
> * * End If
> End Sub
> ' ---------- END OF FILE
>
> HTH
>
> Wouter- Hide quoted text -
>
> - Show quoted text -
Hi Wouter and others,
Sorry, I ran this macro and I am not sure about the *.txt stuff in the
top portion of the macro. Frankly, I am a novice and I don't
understand what the macro really does. This is what I have so far.
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim fname As String
i = 1
'l = i
For k = i To j
j = i + 1
If Range("B" & i).Value <> Range("B" & j).Value Then
Range("B" & k, "Z" & i).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
i = i + 1
Next k
End Sub
The problem with that is I can't get the entire range selected.
Can Wouter or anyone throw some light on this? I would really
appreciate it.
Thanks,
Kevin
|
Similar Threads
