Results 1 to 8 of 8

Assigning the activecell value to a variable

This is a discussion on Assigning the activecell value to a variable within the Excel Questions forums, part of the Question Forums category; Hi all Dead easy one for you. I'm trying to asssign whatever value is in the current active cell to ...

  1. #1
    Board Regular
    Join Date
    Aug 2005
    Posts
    79

    Default Assigning the activecell value to a variable

    Hi all

    Dead easy one for you. I'm trying to asssign whatever value is in the current active cell to my variable b.

    Code:
    Sub Macro1()
    
        Dim a As Range, b As Range
        
        Worksheets("Sheet1").Activate
        Application.ScreenUpdating = False
        ActiveCell.CurrentRegion.Select
        ActiveCell.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
            xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers
        
        For Each a In Range("A2:A" & Range("A65536").End(xlUp).Row)
            a.Value = Trim(a.Value)
            ActiveCell.Offset(0, 1).Activate
            b.Value = Trim(ActiveCell.Value)
        Next a
    
    End Sub
    I get a run-time error '91'

    Cheers
    Mark

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,998

    Default

    If it's not an object variable it won't have a Value property. So try:

    Code:
    b = Trim(ActiveCell.Value)

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,214

    Default

    I think b probably needs to a String. What exactly are you doing?

    PS: you don't need to select the range
    Hope that helps.

    Free Excel sample DataBase Form here

    royUk's web site

    "making Excel work for you"

  4. #4
    New Member
    Join Date
    Sep 2005
    Location
    Mumbai, India
    Posts
    46

    Default

    Hi,
    the reason why you are getting Error 91: "Object variable or With Block variable not set" is because you have declared variable b as a range object and in the later part of your code you have a statement:

    b.Value = Trim(ActiveCell.Value)

    But you have not set variable b to any particular range. So the macro is not able to decide what is the value of range variable b. Your macro runs fine till the Sort part, but only when it reaches the above mentioned line is it not able to determine the value of b because variable b is not set to any range.

  5. #5
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default

    I'm presuming here that what you are attempting to do is to Trim the contents of the cell you refer to as 'b'? Rather than actually assign a value to 'b' from that particular cell? Forgive me if I'm mistaken, but if I am right then you need to modify your code as follows:

    For Each a In Range("A2:A" & Range("A65536").End(xlUp).Row)
    a.Value = Trim(a.Value)
    a.Offset(0, 1).Value = Trim(a.Offset(0, 1).Value)
    Next a

    If this was what you were trying to do, then it was failing as you hadn't defined what b was (you hadn't given it an object reference). There isn't really any need for another object variable 'b' anyhow.

    Regards

    Richard

  6. #6
    Board Regular
    Join Date
    Aug 2005
    Posts
    79

    Default

    Hi both

    Quite right I had to change them to strings. Right here's the scenario - and a complex one at that I think.

    I'm going to have a list of data with 3 columns. After initially sorting the list by column 1 (ID) then column 2 (TYPE) I need to do some string comparing. Basically I cannot have a row (record) where the ID and the TYPE match. For instance:

    • vehicle ID type description
      98 Key1 D1593
      98 Key1 6831
      98 Key1 D1909
      507 Key2 H2432


    The first 3 records match this criteria so on 2 of them I will need to change their TYPE to something other than "Key1". As for what they need to be changed to that could always vary so I think the best bet is to populate any cases it finds to what it already is but prefixed with a "not" before hand. So after running the code I would get:

    • vehicle ID type description
      98 Key1 D1593
      98 not Key1 6831
      98 not Key1 D1909
      507 Key2 H2432


    See where I'm coming from?

  7. #7
    Board Regular
    Join Date
    Aug 2005
    Posts
    79

    Default

    So I've grasped how to get the 2 values for each row now:

    Code:
    Sub Macro1()
    
        Dim a As Range
        Dim a1 As String, a2 As String, a3 As String, b1 As String, b2 As String, b3 As String
        
        Worksheets("Sheet1").Activate
        Application.ScreenUpdating = False
        ActiveCell.CurrentRegion.Select
        ActiveCell.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:= _
            Range("B2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:= _
            xlSortTextAsNumbers, DataOption2:=xlSortNormal
        
        For Each a In Range("A2:A" & Range("A65536").End(xlUp).Row)
            a1 = Trim(a.Value)
            b1 = Trim(a.Offset(0, 1).Value)
        Next a
    
    End Sub
    Now onto the hard bit - comparing all these strings. I only ever really get a maximum of 3 records with the same ID.

    Thanks by the way Parsnip your method worked nicely.

  8. #8
    Board Regular
    Join Date
    Aug 2005
    Posts
    79

    Default

    Hi guys

    Well it was relatively painless really:

    Code:
    Sub check_dups()
    
        Dim a As Range
        Dim a1 As String, a2 As String, a3 As String, b1 As String, b2 As String, b3 As String
        
        Worksheets("Sheet1").Activate
        Application.ScreenUpdating = False
        ActiveCell.CurrentRegion.Select
        ActiveCell.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:= _
            Range("B2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:= _
            xlSortTextAsNumbers, DataOption2:=xlSortNormal
        
        For Each a In Range("A2:A" & Range("A65536").End(xlUp).Row)
            a1 = Trim(a.Value)
            a2 = Trim(a.Offset(1, 0).Value)
            a3 = Trim(a.Offset(2, 0).Value)
            b1 = Trim(a.Offset(0, 1).Value)
            b2 = Trim(a.Offset(1, 1).Value)
            b3 = Trim(a.Offset(2, 1).Value)
            If a1 = a2 And a2 = a3 And b1 = b2 And b2 = b3 Then
                a.Offset(1, 1).Value = "DUPLICATE " & a.Offset(1, 1).Value
                a.Offset(2, 1).Value = "DUPLICATE " & a.Offset(2, 1).Value
                ElseIf a1 = a2 And b1 = b2 Then
                    a.Offset(1, 1).Value = "DUPLICATE " & a.Offset(1, 1).Value
            End If
        Next a
    
    End Sub
    Thanks again for all your help.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com