0

I want the below function to pick data from Row till Column H, however this function is picking details up to the last used cell.

Below are the VBA code I'm using, help me to fix this.

Function getapproverdataHTML() As String

    Dim datacolumn As Range
    Dim datarow As Range
    Dim R As Range
    Dim C As Range
    Dim str As String

    sheets(2).Activate

    Set datacolumn = Range("A1", Range("A1").End(xlDown))

    str = "<table>"

    For Each R In datacolumn
    str = str & "<tr>"
    Set datarow = Range(R, R.End(xlToRight))
    For Each C In datarow
    str = str & "<td>" & C.Value & "</td>"
    Next C
    str = str & "</tr>"
    Next R
    str = str & "</table>"
    getapproverdataHTML = str


End Function
Rajesh Sinha
  • 8,995
  • 6
  • 15
  • 35
PCH
  • 1

1 Answers1

0

If, by cell "H", you really mean the cell in column "H", then try changing your Set datarow line to:

Set datarow = Range(R, R(1, 9).End(xlToLeft))

If you mean something else, please clarify.

BTW, you should use a similar method for datacolumn

Set datacolumn = Range("A1", cells(rows.count,1).End(xlup))
Ron Rosenfeld
  • 8,198
  • 3
  • 13
  • 17
  • You are correct, its Column H. When i cam changing the above range in the function, it keeps on looping without stopping. However it should stop at the first blank cell in column 'A' when ever that cell is empty. Please help on this. – PCH May 04 '20 at 11:57
  • 1
    @PCH See my edit about datacolumn. I cannot reproduce your problem from the information you have provided. Please read the HELP topic for [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve), and provide more information. – Ron Rosenfeld May 04 '20 at 12:01
  • Sub creatingemail() Dim olapp As Outlook.Application Dim olemail As Outlook.MailItem Set olapp = Outlook.Application Set olemail = olapp.CreateItem(olMailItem) With olemail .BodyFormat = olFormatHTML .Display – PCH May 04 '20 at 12:11
  • .To = sheets(2).Range("I2") .CC = "p.ch@test.com" .Subject = "URGENT" .HTMLBody = "Hi,

    " _ & "attention.

    " _ & getapproverdataHTML _ & "
    please do let us know.

    " _ & "Thank you" End With End Sub     – PCH May 04 '20 at 12:11
  • Hi I have shared my complete code with you, its in two parts since the comments section doesn't allow long comments. Please run this and advise. Thank you in advance – PCH May 04 '20 at 12:12
  • 1
    @PCH Adding code in comments is not particularly useful for creating a MCVE. Please **re-read** that link I provided. Just edit your question including **MINIMAL** code and a **data example** that it will work on that shows your problem. To make the data example useful edit your question to post it as text, perhaps using this [Markdown Tables Generator](http://www.tablesgenerator.com/markdown_tables). – Ron Rosenfeld May 04 '20 at 12:39