Assigning the activecell value to a variable

[mcornbill]

Hi all

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

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

 

[Andrew Poulsom]

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

b = Trim(ActiveCell.Value)

 

[royUK]

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

PS: you don't need to select the range

 

[purnima]

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.

 

[Richard Schollar]

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

 

[mcornbill]

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?

 

[mcornbill]

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

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.

 

[mcornbill]

Hi guys

Well it was relatively painless really:

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.