Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|