Solved

VBA FOR EXCEL   Creating a simple MAcro

Posted on 2006-06-20
9
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 20

Accepted Solution

by:
alainbryden earned 75 total points
ID: 16946739
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
ID: 16946756
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
ID: 16951319
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:attachie
ID: 16951390
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
 
LVL 20

Expert Comment

by:alainbryden
ID: 16951922
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
ID: 16951957
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
ID: 16951985
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
ID: 16952075
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
ID: 16953497
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
This is about my first experience with programming Arduino.
Simple Linear Regression
Six Sigma Control Plans

635 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