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



    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:






    Tuesday, September 18, 2007 9:38 PM


  • 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


    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


    Catch ex As Exception



    oSheet = Nothing

    oBook = Nothing


    oExcel = Nothing


    End Try

    End Sub

    End Class



    For more information about excel automation, please visit: 

    Best regards,


    Thursday, September 20, 2007 2:59 AM