Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating and copying data into new sheet
Thank you very much for your help and the code. Works perfectly. I can see how the columns are calculated in the export page - ver impressive. I am perhaps asking the impossible here but here goes... I see in your formula you are looking for an output in each chang within column A, is there anyway you can say in the VBA that you only want to repor each change in column A when, for example column B is 1/12/2003 ? Many thanks again, Scott -- scottwilson ----------------------------------------------------------------------- scottwilsonx's Profile: http://www.excelforum.com/member.php...fo&userid=1112 View this thread: http://www.excelforum.com/showthread.php?threadid=26382 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating and copying data into new sheet
Scott,
Not sure what you mean by change (or report), but you can use something like the below. Note that the date in the line If CLng(myCell(1, 2).Value) = CLng(DateValue("12/1/2003")) Then must be entered as a US-style date: mm/dd/yyyy, not dd/mm/yyyy - that's just VBA. HTH, Bernie MS Excel MVP Sub TryNow2() Dim myCell As Range Dim myRange As Range Dim i As Long Dim mySht As Worksheet Dim DataSht As Worksheet Set DataSht = ActiveSheet 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 CLng(myCell(1, 2).Value) = CLng(DateValue("12/1/2003")) 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, 5).Value Next i End If Next myCell mySht.Range("B:B").NumberFormat = "dd/mm/yyyy" End Sub "scottwilsonx" wrote in message ... Thank you very much for your help and the code. Works perfectly. I can see how the columns are calculated in the export page - very impressive. I am perhaps asking the impossible here but here goes... I see in your formula you are looking for an output in each change within column A, is there anyway you can say in the VBA that you only want to report each change in column A when, for example column B is 1/12/2003 ? Many thanks again, Scott. -- scottwilsonx ------------------------------------------------------------------------ scottwilsonx's Profile: http://www.excelforum.com/member.php...o&userid=11128 View this thread: http://www.excelforum.com/showthread...hreadid=263823 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
Copying the repeated data of the previous sheet to the next sheet | Excel Discussion (Misc queries) | |||
Copying Data from one sheet to another sheet on a specific day | Excel Worksheet Functions | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
Calculating and copying data into new sheet | Excel Programming |