Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a text string while looping though a worksheet
I am tring to build up a text string of cells a-g for all rows that have
todays date in col A The code below is what I have come up with, but it seems to hang... Help and TIA Phil "**************** ThisWorkbook.Sheets("New15Min").Activate With Worksheets("New15Min").Range("a1:a64000") MyHtm15 = "" MyHtm15 = MyHtm15 & "</p" Set c = .Find(Date, LookIn:=xlValues) If Not c Is Nothing Then 'firstAddress = c.Address Do MyHtm15 = MyHtm15 & ActiveCell(0, 0) & ActiveCell(0, 1) & ActiveCell(0, 2) & ActiveCell(0, 3) & ActiveCell(0, 4) & ActiveCell(0, 5) & ActiveCell(0, 6) & "</p" 'c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing 'And c.Address < firstAddress End If End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a text string while looping though a worksheet
Hello Philips,
Dim MyHtm15 As String Dim firstAddress As String Dim i As Long Dim c As Range ThisWorkbook.Sheets("New15Min").Activate With Worksheets("New15Min").Range("a1:a64000") MyHtm15 = "" MyHtm15 = MyHtm15 & "</p" Set c = .Find(Date, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do For i = 0 To 6 MyHtm15 = MyHtm15 & c.Offset(0, i).Value Next MyHtm15 = MyHtm15 & "</p" Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Set c = Nothing End With "Phillips" wrote in message news:78zBb.492342$Tr4.1344946@attbi_s03... I am tring to build up a text string of cells a-g for all rows that have todays date in col A The code below is what I have come up with, but it seems to hang... Help and TIA Phil "**************** ThisWorkbook.Sheets("New15Min").Activate With Worksheets("New15Min").Range("a1:a64000") MyHtm15 = "" MyHtm15 = MyHtm15 & "</p" Set c = .Find(Date, LookIn:=xlValues) If Not c Is Nothing Then 'firstAddress = c.Address Do MyHtm15 = MyHtm15 & ActiveCell(0, 0) & ActiveCell(0, 1) & ActiveCell(0, 2) & ActiveCell(0, 3) & ActiveCell(0, 4) & ActiveCell(0, 5) & ActiveCell(0, 6) & "</p" 'c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing 'And c.Address < firstAddress End If End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a text string while looping though a worksheet
make sure that you have Options Explocit at th etop of
the module and that you DIM MyHtm15 As String you REM'd out the address & test. put them back, otherwise your loop won't exit ! Note that each time that you do a find, the variable 'c' is the cell found. add this as well dim text as string we'll build the values in 'text' then add it to your main string. text = "" for myoffset = 0 to 5 text = text & "," & c.offset(0,myoffset).Value next myoffset MyHtm15 = MyHtm15 & vbcr & mid(text,2) note that here we build the 'text' string by adding the ',' before each value. teh MID() function strips off the preceding ',' as we don't need it....its easier that stripping off a final ',' it it had been appended. HTH Patrick Molloy Microsoft Excel MVP -----Original Message----- I am tring to build up a text string of cells a-g for all rows that have todays date in col A The code below is what I have come up with, but it seems to hang... Help and TIA Phil "**************** ThisWorkbook.Sheets("New15Min").Activate With Worksheets("New15Min").Range("a1:a64000") MyHtm15 = "" MyHtm15 = MyHtm15 & "</p" Set c = .Find(Date, LookIn:=xlValues) If Not c Is Nothing Then 'firstAddress = c.Address Do MyHtm15 = MyHtm15 & ActiveCell(0, 0) & ActiveCell(0, 1) & ActiveCell(0, 2) & ActiveCell(0, 3) & ActiveCell(0, 4) & ActiveCell(0, 5) & ActiveCell(0, 6) & "</p" 'c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing 'And c.Address < firstAddress End If End With . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a text string while looping though a worksheet
Me again, if you have a lot of data in the worksheet, using array makes code
faster. I don't see your worksheet so it's not sure.. but which one is faster in your case? :) '--------------------------------------------------------------------------- ----- Dim MyHtm15 As String Dim firstAddress As String Dim buf As Variant Dim i As Long Dim c As Range With Worksheets("New15Min") .Activate buf = .Range(.[A1], .[A65536].End(xlUp).Offset(, 6)).Value End With MyHtm15 = vbNullString: MyHtm15 = MyHtm15 & "</p" With Columns(1) Set c = .Find(Date, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do For i = 1 To 7 MyHtm15 = MyHtm15 & buf(c.Row, i) Next MyHtm15 = MyHtm15 & "</p" Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Set c = Nothing '--------------------------------------------------------------------------- ----- -- Kind Regards Colo /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Colo of 'The Road of The Cell Masters' :) URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
building a text string while looping though a worksheet
Colo,
Thank you very much! This does what I wanted, however, the results are not as "neat" as I hoped for,,, I am trying to build up an email. How could I insert this in the middle of my email as "formatted" or as a table? I also tried formatting using vbtab, but that did not do much.. Again, I thank you, just I thought I would be happy with the results I would have gotten, but, it don't look pretty... Phil "Colo" wrote in message ... Hello Philips, Dim MyHtm15 As String Dim firstAddress As String Dim i As Long Dim c As Range ThisWorkbook.Sheets("New15Min").Activate With Worksheets("New15Min").Range("a1:a64000") MyHtm15 = "" MyHtm15 = MyHtm15 & "</p" Set c = .Find(Date, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do For i = 0 To 6 MyHtm15 = MyHtm15 & c.Offset(0, i).Value Next MyHtm15 = MyHtm15 & "</p" Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If Set c = Nothing End With "Phillips" wrote in message news:78zBb.492342$Tr4.1344946@attbi_s03... I am tring to build up a text string of cells a-g for all rows that have todays date in col A The code below is what I have come up with, but it seems to hang... Help and TIA Phil "**************** ThisWorkbook.Sheets("New15Min").Activate With Worksheets("New15Min").Range("a1:a64000") MyHtm15 = "" MyHtm15 = MyHtm15 & "</p" Set c = .Find(Date, LookIn:=xlValues) If Not c Is Nothing Then 'firstAddress = c.Address Do MyHtm15 = MyHtm15 & ActiveCell(0, 0) & ActiveCell(0, 1) & ActiveCell(0, 2) & ActiveCell(0, 3) & ActiveCell(0, 4) & ActiveCell(0, 5) & ActiveCell(0, 6) & "</p" 'c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing 'And c.Address < firstAddress End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building a table from a list on another worksheet | Excel Discussion (Misc queries) | |||
building comments from text cells | Excel Worksheet Functions | |||
Building criteria string for Advanced Filter variable not resolvin | Excel Discussion (Misc queries) | |||
Building Sum by Matching String | Excel Discussion (Misc queries) | |||
Building a dropdown from a worksheet | Excel Programming |