locked
How can I return a range object in Excel RRS feed

  • Question

  • Every reference to using the Range object requires the reference "RC" for Example Range("A5"). Does any one know how I can use the Cell's rwIndex, colIndex to return a range that I can use in the above. For example I tried the following:

     

    Dim rtmp As Range,

    Dim rwIndex As Integer

    DIM colIndex As Integer

     

    rwIndex = 1

    colIndex = 5

     

    Worksheets("Sheet1").Activate

    rtemp = Worksheets("Sheet1").Range(Cells(rwIndex,colIndex) ' which I expected to return "A5"

     

    but I get Run-time error 91 Object Variable or With block variable not set.

     

    What I am trying to do Is to locate a cell (rwindex,colindex) with a specific value (e.g., "company") and then I want to select the cell so I can use the Fill option to assign values to the cells such as in:

     

    Worksheets("Sheet1").Range("A5:A10").FillDown

     

    Thanks


     

    Tuesday, September 18, 2007 9:38 PM

Answers

  • Hi K2k9,

     

    Based on your post, you need to use the Range object to refer some cells. Here is the code snippet about how to use the Range object to set the color of some cells. Hope this helps.

    Code Snippet

    Imports Microsoft.Office.Interop.Excel

    Imports Microsoft.Office.Interop

    Public Class Form1

    Dim oExcel As Microsoft.Office.Interop.Excel.Application

    Dim oBook, oBook1 As Microsoft.Office.Interop.Excel.Workbook

    Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try

    oExcel = New Microsoft.Office.Interop.Excel.Application

    oBook = oExcel.Workbooks.Add

    oBook1 = oExcel.Workbooks.Add

    oSheet = CType(oBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)

    oSheet.Range("A1").Value = "Last Name"

    Dim rg As Microsoft.Office.Interop.Excel.Range = oSheet.Range("D1:D4")

    rg.Interior.Color = System.Drawing.Color.Red.ToArgb()

    rg = oSheet.Range("B1")

    oSheet.Range("B1").Value = "First Name"

    oSheet.Range("C1").Value = "Price"

    oSheet.Range("A1:B1").Font.Bold = True

    oSheet.Range("A2").Value = "Doe"

    oSheet.Range("B2").Value = "John"

    oSheet.Range("C2").Value = 12345.456

    oSheet.Range("C2").Cells.NumberFormat = "$0.00"

    oExcel.DisplayAlerts = False

    oBook.SaveAs("c:\Book1.xlsx")

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    Finally

    oSheet = Nothing

    oBook = Nothing

    oExcel.Quit()

    oExcel = Nothing

    GC.Collect()

    End Try

    End Sub

    End Class

     

     

    For more information about excel automation, please visit: 

    Best regards,

    Riquel.

    Thursday, September 20, 2007 2:59 AM