Solved

VBA FOR EXCEL   Creating a simple MAcro

Posted on 2006-06-20
9
268 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:attachie
  • 5
  • 4
9 Comments
 
LVL 20

Accepted Solution

by:
alainbryden earned 75 total points
Comment Utility
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
 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
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
 

Author Comment

by:attachie
Comment Utility
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
 

Author Comment

by:attachie
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
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
 

Author Comment

by:attachie
Comment Utility
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
 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
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
 

Author Comment

by:attachie
Comment Utility
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
 
LVL 20

Expert Comment

by:alainbryden
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now