Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Please help me in Macros
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 103
Default Please help me in Macros
I am having a report like this

03:30 10 Mar 05 Mar 31 Mar
Hourly 368 369 259
Confirmed 2383 2317 1773
Cancelled( this Hr.) 38 42 47
Cancelled Total 331 413 362
Hourly Cancel in % 10% 11% 18%
Pending (this Hr.) 29 3 12
Pending total 218 164 233
Hourly pending (%) 8% 1% 5%
Homepage(visit) 50051 48747 51728
Search results(visit) 25936 24903 28373
Review Page (visit) 8731 8544 8285
Hourly Home 7109 7482 7842
Hourly Search 3694 3812 4347
Hourly Review 1224 1361 1130
Ratio of HS & HP 0.5196 0.5095 0.5543
Ratio of HR & HP 0.1722 0.1819 0.1441
Conversion(this hour) 0.0996 0.0968 0.0596
Total Conversion 0.0919 0.0930 0.0625
Review Conversion 0.3007 0.2711 0.2292

& I have this data for every hour 9:30, 10:30, 11:30 , so on. I want to make
like this.

Saturday 2-Feb 9:30AM Confirmed 117 Daily
Saturday 2-Feb 9:30AM Home Page 2336 Daily
Saturday 2-Feb 9:30AM Search 1236 Daily
Saturday 2-Feb 9:30AM Ratio 0.5291 Daily
Saturday 2-Feb 9:30AM Conversion 0.0947 Daily
Saturday 2-Feb 9:30AM Confirmed 87 Hourly
Saturday 2-Feb 9:30AM Home Page 7680 Hourly
Saturday 2-Feb 9:30AM Search 3914 Hourly
Saturday 2-Feb 9:30AM Ratio 0.509635417 Hourly
Saturday 2-Feb 9:30AM Conversion 0.0956 Hourly

Please help me is there any macro who help me.

Thanks with Regards
Dinesh
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Please help me in Macros
Sub transpose_data()

ColCount = 2
Sh2RowCount = 1
With Sheets("Sheet1")
MyTime = .Range("A1")
Do While .Cells(1, ColCount) < ""
MyDate = .Cells(1, ColCount)
DayofWeek = WeekdayName(Weekday(MyDate))
RowCount = 2
Do While .Range("A" & RowCount) < ""
Task = .Range("A" & RowCount)
Amount = .Cells(RowCount, ColCount)
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = DayofWeek
.Range("B" & Sh2RowCount) = MyDate
.Range("C" & Sh2RowCount) = MyTime
.Range("D" & Sh2RowCount) = Task
.Range("E" & Sh2RowCount) = Amount
Sh2RowCount = Sh2RowCount + 1
End With
RowCount = RowCount + 1
Loop
ColCount = ColCount + 1
Loop

End With

End Sub





"Dinesh" wrote:

I am having a report like this

03:30 10 Mar 05 Mar 31 Mar
Hourly 368 369 259
Confirmed 2383 2317 1773
Cancelled( this Hr.) 38 42 47
Cancelled Total 331 413 362
Hourly Cancel in % 10% 11% 18%
Pending (this Hr.) 29 3 12
Pending total 218 164 233
Hourly pending (%) 8% 1% 5%
Homepage(visit) 50051 48747 51728
Search results(visit) 25936 24903 28373
Review Page (visit) 8731 8544 8285
Hourly Home 7109 7482 7842
Hourly Search 3694 3812 4347
Hourly Review 1224 1361 1130
Ratio of HS & HP 0.5196 0.5095 0.5543
Ratio of HR & HP 0.1722 0.1819 0.1441
Conversion(this hour) 0.0996 0.0968 0.0596
Total Conversion 0.0919 0.0930 0.0625
Review Conversion 0.3007 0.2711 0.2292

& I have this data for every hour 9:30, 10:30, 11:30 , so on. I want to make
like this.

Saturday 2-Feb 9:30AM Confirmed 117 Daily
Saturday 2-Feb 9:30AM Home Page 2336 Daily
Saturday 2-Feb 9:30AM Search 1236 Daily
Saturday 2-Feb 9:30AM Ratio 0.5291 Daily
Saturday 2-Feb 9:30AM Conversion 0.0947 Daily
Saturday 2-Feb 9:30AM Confirmed 87 Hourly
Saturday 2-Feb 9:30AM Home Page 7680 Hourly
Saturday 2-Feb 9:30AM Search 3914 Hourly
Saturday 2-Feb 9:30AM Ratio 0.509635417 Hourly
Saturday 2-Feb 9:30AM Conversion 0.0956 Hourly

Please help me is there any macro who help me.

Thanks with Regards
Dinesh
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 103
Default Please help me in Macros
Its not working

"Joel" wrote:

Sub transpose_data()

ColCount = 2
Sh2RowCount = 1
With Sheets("Sheet1")
MyTime = .Range("A1")
Do While .Cells(1, ColCount) < ""
MyDate = .Cells(1, ColCount)
DayofWeek = WeekdayName(Weekday(MyDate))
RowCount = 2
Do While .Range("A" & RowCount) < ""
Task = .Range("A" & RowCount)
Amount = .Cells(RowCount, ColCount)
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = DayofWeek
.Range("B" & Sh2RowCount) = MyDate
.Range("C" & Sh2RowCount) = MyTime
.Range("D" & Sh2RowCount) = Task
.Range("E" & Sh2RowCount) = Amount
Sh2RowCount = Sh2RowCount + 1
End With
RowCount = RowCount + 1
Loop
ColCount = ColCount + 1
Loop

End With

End Sub





"Dinesh" wrote:

I am having a report like this

03:30 10 Mar 05 Mar 31 Mar
Hourly 368 369 259
Confirmed 2383 2317 1773
Cancelled( this Hr.) 38 42 47
Cancelled Total 331 413 362
Hourly Cancel in % 10% 11% 18%
Pending (this Hr.) 29 3 12
Pending total 218 164 233
Hourly pending (%) 8% 1% 5%
Homepage(visit) 50051 48747 51728
Search results(visit) 25936 24903 28373
Review Page (visit) 8731 8544 8285
Hourly Home 7109 7482 7842
Hourly Search 3694 3812 4347
Hourly Review 1224 1361 1130
Ratio of HS & HP 0.5196 0.5095 0.5543
Ratio of HR & HP 0.1722 0.1819 0.1441
Conversion(this hour) 0.0996 0.0968 0.0596
Total Conversion 0.0919 0.0930 0.0625
Review Conversion 0.3007 0.2711 0.2292

& I have this data for every hour 9:30, 10:30, 11:30 , so on. I want to make
like this.

Saturday 2-Feb 9:30AM Confirmed 117 Daily
Saturday 2-Feb 9:30AM Home Page 2336 Daily
Saturday 2-Feb 9:30AM Search 1236 Daily
Saturday 2-Feb 9:30AM Ratio 0.5291 Daily
Saturday 2-Feb 9:30AM Conversion 0.0947 Daily
Saturday 2-Feb 9:30AM Confirmed 87 Hourly
Saturday 2-Feb 9:30AM Home Page 7680 Hourly
Saturday 2-Feb 9:30AM Search 3914 Hourly
Saturday 2-Feb 9:30AM Ratio 0.509635417 Hourly
Saturday 2-Feb 9:30AM Conversion 0.0956 Hourly

Please help me is there any macro who help me.

Thanks with Regards
Dinesh
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Please help me in Macros
The 3 things to check
1) The code is moving data from Sheet 1 to sheet 2. the sheet names need to
be changed in the code if you are using differrent sheet names
2) The code is expecting the data to start in Row 2 of column A. It also
expects the header row with the dates to be in row 1.
3) The code doesn't expect blank rows in column A between the data. It also
expect no blank cells in Row 1 starting in column B

I put the time 3:30 in cell A1 of sheet1 with data in every row and every
column.

"Dinesh" wrote:

Its not working

"Joel" wrote:

Sub transpose_data()

ColCount = 2
Sh2RowCount = 1
With Sheets("Sheet1")
MyTime = .Range("A1")
Do While .Cells(1, ColCount) < ""
MyDate = .Cells(1, ColCount)
DayofWeek = WeekdayName(Weekday(MyDate))
RowCount = 2
Do While .Range("A" & RowCount) < ""
Task = .Range("A" & RowCount)
Amount = .Cells(RowCount, ColCount)
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = DayofWeek
.Range("B" & Sh2RowCount) = MyDate
.Range("C" & Sh2RowCount) = MyTime
.Range("D" & Sh2RowCount) = Task
.Range("E" & Sh2RowCount) = Amount
Sh2RowCount = Sh2RowCount + 1
End With
RowCount = RowCount + 1
Loop
ColCount = ColCount + 1
Loop

End With

End Sub





"Dinesh" wrote:

I am having a report like this

03:30 10 Mar 05 Mar 31 Mar
Hourly 368 369 259
Confirmed 2383 2317 1773
Cancelled( this Hr.) 38 42 47
Cancelled Total 331 413 362
Hourly Cancel in % 10% 11% 18%
Pending (this Hr.) 29 3 12
Pending total 218 164 233
Hourly pending (%) 8% 1% 5%
Homepage(visit) 50051 48747 51728
Search results(visit) 25936 24903 28373
Review Page (visit) 8731 8544 8285
Hourly Home 7109 7482 7842
Hourly Search 3694 3812 4347
Hourly Review 1224 1361 1130
Ratio of HS & HP 0.5196 0.5095 0.5543
Ratio of HR & HP 0.1722 0.1819 0.1441
Conversion(this hour) 0.0996 0.0968 0.0596
Total Conversion 0.0919 0.0930 0.0625
Review Conversion 0.3007 0.2711 0.2292

& I have this data for every hour 9:30, 10:30, 11:30 , so on. I want to make
like this.

Saturday 2-Feb 9:30AM Confirmed 117 Daily
Saturday 2-Feb 9:30AM Home Page 2336 Daily
Saturday 2-Feb 9:30AM Search 1236 Daily
Saturday 2-Feb 9:30AM Ratio 0.5291 Daily
Saturday 2-Feb 9:30AM Conversion 0.0947 Daily
Saturday 2-Feb 9:30AM Confirmed 87 Hourly
Saturday 2-Feb 9:30AM Home Page 7680 Hourly
Saturday 2-Feb 9:30AM Search 3914 Hourly
Saturday 2-Feb 9:30AM Ratio 0.509635417 Hourly
Saturday 2-Feb 9:30AM Conversion 0.0956 Hourly

Please help me is there any macro who help me.

Thanks with Regards
Dinesh
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macros in Personal.xls that would create two toolbars and buttonswith assigned macros Brian Day Excel Programming 1 March 29th 07 11:20 PM
choose default macros Not Enabled / Macros Enable Setting BEEJAY Excel Programming 2 June 30th 06 01:07 PM
weird saving of a document with macros resulting with macros being transfered to the copy alfonso gonzales Excel Programming 0 December 12th 04 09:19 PM
convert lotus 123w macros to excel macros rpiescik Excel Programming 1 September 18th 04 01:35 PM
Macros not appearing in the Tools Macro Macros list hglamy[_2_] Excel Programming 5 October 24th 03 09:10 AM


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement