help with vba please
 

Hi everyone, I would like your expert help with the following:

I have a VBA macro that is based on information in a sheet calle
"import".
The macro looks at the source data in "import" and everytime there is
change of identifier in column A it increments rows in a sheet calle
""new data set" with the identifier plus the contents of column B, C,
& F. The number of increments made is based on the difference betwee
the dates displayed in column B & C.

I also have a listbox called listbox1 which is filled with values fro
BA1:BA301 and these values relate to column F in the "import
worksheet.

The following code isn't working, in that it doesnt pull across an
data based on the selection made in the listbox1.


Sub CreateData()
'Dim x As String
Dim consname As String
Dim myCell As Range
Dim myRange As Range
Dim i As Long
Dim mySht As Worksheet
Dim DataSht As Worksheet
Set DataSht = ActiveSheet
consname = Sheet1.ListBox1.Value
On Error Resume Next
Worksheets("New Data Set").Delete
Set mySht = Worksheets.Add
mySht.Name = "New Data Set"
For Each myCell In DataSht.Range(DataSht.Range("A2"), _
DataSht.Range("A65536").End(xlUp))
If myCell(1, 5).Value = consname Then
For i = CLng(myCell(1, 2).Value) To CLng(myCell(1, 3).Value)
mySht.Range("A65536").End(xlUp)(2).Value = myCell.Value
mySht.Range("B65536").End(xlUp)(2).Value = i
mySht.Range("C65536").End(xlUp)(2).Value = myCell(1, 4).Value
mySht.Range("D65536").End(xlUp)(2).Value = myCell(1, 8).Value
mySht.Range("E65536").End(xlUp)(2).Value = myCell(1, 6).Value
Next i
End If
Next myCell

mySht.Range("B:B").NumberFormat = "dd/mm/yyyy"
End Sub


Any help gratefully received. Thanks
Scott

--
scottwilson
-----------------------------------------------------------------------
scottwilsonx's Profile: http://www.excelforum.com/member.php...fo&userid=1112
View this thread: http://www.excelforum.com/showthread.php?threadid=26432

help with vba please
 

I think perhaps the problem is here?:

For Each myCell In DataSht.Range(DataSht.Range("A2"), _
DataSht.Range("A65536").End(xlUp))
If myCell(1, 5).Value = consname Then
....

When you are trying to match myCell(1,5) to the ListBox entry(consname) you
are looking in the row below myCell, and I think you want to be looking in
the same row (i.e. myCell(0,5)), correct?

K Dales





"scottwilsonx" wrote:


Hi everyone, I would like your expert help with the following:

I have a VBA macro that is based on information in a sheet called
"import".
The macro looks at the source data in "import" and everytime there is a
change of identifier in column A it increments rows in a sheet called
""new data set" with the identifier plus the contents of column B, C, H
& F. The number of increments made is based on the difference between
the dates displayed in column B & C.

I also have a listbox called listbox1 which is filled with values from
BA1:BA301 and these values relate to column F in the "import"
worksheet.

The following code isn't working, in that it doesnt pull across any
data based on the selection made in the listbox1.


Sub CreateData()
'Dim x As String
Dim consname As String
Dim myCell As Range
Dim myRange As Range
Dim i As Long
Dim mySht As Worksheet
Dim DataSht As Worksheet
Set DataSht = ActiveSheet
consname = Sheet1.ListBox1.Value
On Error Resume Next
Worksheets("New Data Set").Delete
Set mySht = Worksheets.Add
mySht.Name = "New Data Set"
For Each myCell In DataSht.Range(DataSht.Range("A2"), _
DataSht.Range("A65536").End(xlUp))
If myCell(1, 5).Value = consname Then
For i = CLng(myCell(1, 2).Value) To CLng(myCell(1, 3).Value)
mySht.Range("A65536").End(xlUp)(2).Value = myCell.Value
mySht.Range("B65536").End(xlUp)(2).Value = i
mySht.Range("C65536").End(xlUp)(2).Value = myCell(1, 4).Value
mySht.Range("D65536").End(xlUp)(2).Value = myCell(1, 8).Value
mySht.Range("E65536").End(xlUp)(2).Value = myCell(1, 6).Value
Next i
End If
Next myCell

mySht.Range("B:B").NumberFormat = "dd/mm/yyyy"
End Sub


Any help gratefully received. Thanks
Scott.


--
scottwilsonx
------------------------------------------------------------------------
scottwilsonx's Profile: http://www.excelforum.com/member.php...o&userid=11128
View this thread: http://www.excelforum.com/showthread...hreadid=264323