VBA FOR EXCEL Creating a simple MAcro

i am trying to write a macro that state if cell "C10" = "CBS" then
select cell C11 (which is on another worksheet )and show me the detail of cell c11 (drill down)


this is what i have thus far, but i am getting a type mismatch error


Private Sub CommandButton1_Click()    
    If Cells("C10") = "CBS" Then                        
   
    Cells(i, 2).Select  ' becuase this is located on a different worksheet
    Selection.ShowDetail = True
                       
  End If
End Sub
attachieAsked:
Who is Participating?
 
alainbrydenConnect With a Mentor Commented:
You can't say this:
If Cells("C10") = "CBS" Then                        

When stating the location like this you need to use the code:

If Range("C10") = "CBS" Then

also you can't say:
 Cells(i, 2).Select
unless i is an integer you have predefined. If you meant "column 'I' " then you need to specify it as a number, so
Cells(9, 2).select. This wouldn't make much sense anyways since you are supposed to specify the row index before the column index, so Cells(2,9) would be correct.

Since you prefer to stick with letter denotations though, you are better off making your program say:

Private Sub CommandButton1_Click()    
    If Range("C10") = "CBS" Then                        
         Range("I2").Select
         Selection.ShowDetail = True
    End If
End Sub

or something to that effect, depending on what you're trying to accomplish.

The Type Mismatch error is because Cells() expects integers, and you gave it a string.
0
 
alainbrydenCommented:
OH one more thing!

Cells(...) = "CBS" Does not work.
You need to say Cells(...).value = "CBS"
a cell has manu properties, and what you wish to set is the property value.
That is more likely where the type mismatch error came from.
0
 
attachieAuthor Commented:
thanks, for the corrections however i think you mis understood one part of my problem..

The section where i state
 Cells(i, 2).Select  ' becuase this is located on a different worksheet
    Selection.ShowDetail = True

your correction was

Range("I2").Select
         Selection.ShowDetail = True

however, this just points to the same sheet cell I2.

i need for it to lookup on tab(worksheet) "PVT_DC" scroll and look for "CBS" in colum A. When it find CBS in colume A drill down on cell A? (which ever row it find the data in....(in this case drill down on $7 to see what is included in the $7)


EX:
      A     B     C      D     E      F
1  abc    $1
2  nbc    $5
3  dcf     $6
4  CBS   $7
5
6
7
8

 

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
attachieAuthor Commented:
For i = 1 To [m65535].End(xlUp).Row
 
                     Select Case ActiveSheet.Cells(i, 2)
                         Case "LAND AMERICA ONE STOP":
                            'Range("I2").Select
                           'Selection.ShowDetail = True
                        MsgBox "found LAnd America"
                        End Select
                        Next i
0
 
alainbrydenCommented:
oooh to look at another sheet you need to find the sheet you're looking for.

Let's say that "PVT_DC" is the 3rd worskeet open (I don't know, but you just tell by how many tabs are at the bottom). It's one of those anyways, sheet 1, 2, 3 whatever.

Then you say
Worksheets(3).Activate
Worksheets(3).Range("I2").Select

And now you have control over the right cell in the right worksheet. Hope that helps.

I'm not sure why you want to select I2, but as for the rest of your problem...


As long as you refer to the right worksheet, say worksheet(1), then you can follow the rest of the commands so that you're searching on that worksheet. so

Dim I As integer
Dim whatImlookingfor As String
with Worksheet(1)
      for i = 0 to .activeRange.rows
           if .Cells( i, 1 ).value = "CBS" then
                 whatImlookingfor = .Cells( i, 2).value
                 exit for
           end if
end with

I see now, in your example before i was the row index, not the column name. So then what I said above about range("I2") doesn't really apply, I misunderstood what you're doing. But now that I see what you're doing I think that is the correct answer that you want, the code that I wrote above. Give it a shot, it should return "7$" or whatever is in the cell next to the cell CBS in the worksheet. Remember to set the number for the worksheet to that of the PVT_DC worksheet, whatever it is.
0
 
attachieAuthor Commented:
correct me if i wrong but i think you think i am working with 2 different workbooks....

i am working with 2 worksheet (in one workbook) one name

1. PVT_DC
2. DC_invoice


the command button is in DC_INVOICE however, i need the macro to look to
the first worksheet (PVT_DC)

is that possible

all of this should be occuring behind a command button


i want the event to look through the entire A colume (A:A) for the name "and america"
if it find the name i want it to take the data from the next cell over: which is colume ("B?")

for example:

      A                   B     C      D     E      F
1  abc                 $1
2  nbc                 $5
3  dcf                  $6
4 landamerica    *$7*
5
6
7
8
0
 
alainbrydenCommented:
okay yeah I think you were typing as I was typing, but like I said, since PVT_DC is the first worksheet in your workbook, you want to say:

Dim I As integer
Dim whatImlookingfor As String

with Worksheets(1)
      for i = 0 to .activeRange.rows
           if .Cells( i, 1 ).value = "CBS" then
                 whatImlookingfor = .Cells( i, 2).value
                 exit for
           end if
end with
0
 
attachieAuthor Commented:
I AM GETTING AN ERROR  "method not supported"

Private Sub CommandButton1_Click()
Dim I As Integer
Dim whatImlookingfor As String

With Worksheets(1)  '' should i change the 1 to PVT_DC, since that is the name of the worksheet
      For I = 0 To .activeRange.Rows
           If .Cells(I, 1).Value = "CBS" Then
                 whatImlookingfor = .Cells(I, 2).Value
                 
           End If
           Next I
End With

End Sub
0
 
alainbrydenCommented:
no, I'm positive that the specification for worksheets is a number, I'm using it in a visual basic program I'm writing right now. Where is the method not supported error being thrown. Is it on the worksheets function?

Worksheets(num) returns a reference to an Excel.Worksheet so that should be correct.

if the worksheets() function is not recognised, then you must be implementing it in a weird way. Just to test, I made a new macro in worksheet 1 that went like this:
Sub Macro1()
    Worksheets(2).Range("D1").Value = "Hello World"
End Sub

And sure enough, when I ran this from worksheet(1), Hello world appeared in D1 of worksheet(2), so I know that much works.



Ah I just ran what I gave you and saw that where the error occurs is:
               with Worksheets(1)
                           ...   .activeRange.Rows

I appologise, I forgot that the correct way to write this is not active range, but used range, and with a count, so:

         .UsedRange.Rows.count


So now try the following:



Private Sub CommandButton1_Click()
Dim I As Integer
Dim whatImlookingfor As String

With Worksheets(1)
      For I = 0 To .UsedRange.Rows.count
           If .Cells(I, 1).Value = "CBS" Then
                 whatImlookingfor = .Cells(I, 2).Value
                 MsgBox whatImlookingfor
                 Exit For
           End If
      Next I
End With

End Sub


This will display the value next to the cell you want. : ) have fun
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.