current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

In my code, I have declared these variables:

Dim Field_Name, Datatype, row As Integer

Then, inside a For loop, I have this code:

Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
row = Worksheets(i).UsedRange.Find("Field Name").row + 1

However, that code throws the "Object variable or with block variable not set" run-time error. According to the API, the Range.Column and Range.row property is a read-only Long. I have tried making the datatype of my variables to Long, but with no success. It would appear that VBA expecting me to do

Set Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
Set row = Worksheets(i).UsedRange.Find("Field Name").row + 1

However, said variables are not objects, so doing that throws the "Object required" compile error.

Any help with this would be greatly appreciated. If you're not sure about how to fix it, then any workarounds or alternative ways to get the column number and row number of a cell would be greatly appreciated.

share|improve this question
    
Change your 'Dim' to three Dim's with only one variable name and type per Dim. Then try again. If still have error, then which line is it on? Reference: msdn.microsoft.com/en-us/library/office/… –  Wayne G. Dunn Mar 29 at 18:38
3  
the most likely issue of your error is that Find returns Nothing, i.e. string "Field Name" or "Datatype" not found –  simoco Mar 29 at 18:47
    
It would be good to see the rest of your code, as sometimes "unclosed" loops or with statements can cause that kind of error. But I would agree with simoco in that your Find is probably Finding nothing. –  Ron Rosenfeld Mar 29 at 19:07
    
Also, once you get that error straightened out, you should rethink declaring your variables as type Integer. The Column should be OK, but if you are using a version of Excel 2007+, there are more than 32,768 rows; so if your row of interest is further down, you will get an overflow error. –  Ron Rosenfeld Mar 29 at 19:13
    
One other critique: Dim Field_Name, Datatype, row As Integer only declares 'row' as Integer; the other two will be of type Variant. Look at help for information on the Dim statement. –  Ron Rosenfeld Mar 29 at 19:15

2 Answers 2

up vote 1 down vote

What about the below code:

    For i = 1 to 1 ' change to the number of sheets in the workbook
    Set oLookin1 = Worksheets(i).UsedRange
    sLookFor1 = "Field Name"
    Set oFound1 = oLookin1.Find(What:=sLookFor1, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

    If Not oFound1 Is Nothing Then
    Field_Name = oFound1.Column
    RRow = oFound1.Row +1

' code goes here

    Else
    Msgbox "Field Name was not found in Sheet #" & i
    End If

    Set oLookin2 = Worksheets(i).UsedRange
    sLookFor2 = "Datatype"
    Set oFound2 = oLookin2.Find(What:=sLookFor2, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

    If Not oFound2 Is Nothing Then
    DataType = oFound2.Column

' code goes here

    Else
    Msgbox "Datatype was not found in Sheet #" & i
    End If
    Next i
share|improve this answer
    
+ 1 BirdsView: This should work. It might be an icing on the cake if you declare your variables and indent your code ;) –  Siddharth Rout Mar 30 at 5:26
up vote -1 down vote

You ARE using VBA, right?

I would avoid Dim'ing row as a variable.............The following works if the texts are on the sheets:

Sub qwerty()
    For i = 1 To 1
        Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
        DataType = Worksheets(i).UsedRange.Find("Datatype").Column
        RRow = Worksheets(i).UsedRange.Find("Field Name").Row + 1
        MsgBox Field_Name & vbCrLf & DataType & vbCrLf & RRow
    Next i
End Sub

EDIT#1

make sure the strings you are trying to Find are in each worksheet in your loop.

share|improve this answer
    
Yes, I am positive I am using VBA. Renaming row to something else didn't work, unfortunately. –  7yphoid Mar 29 at 18:48
    
I can re-produce your error message if I make the Find fail.........make sure the text you are looking for is on each worksheet in the loop. –  Gary's Student Mar 29 at 19:18
    
Why avoid declaring any variable? I would declare row as being of Type Long, since there can be more than 32768 rows in Excel 2007 or later, but isn't it good practice to declare variables? –  Ron Rosenfeld Mar 29 at 19:18
    
You are correct! Variables should be declared. I was trying to replicate the Poster's error message........The only way I can do that is to make the Find fail. –  Gary's Student Mar 29 at 19:24
1  
an other guess is thet worksheet(i) does not exist (i too big) : make sure i is not bigger than thisworkbook.worksheets.count –  Patrick Lepelletier Mar 30 at 11:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.