FIND character in variable VBA

[WarPigl3t]

I don't want to find a character in a cell
I type this function into cell D1... =Mjolnir(A1:B1)

Function Mjolnir(a As Excel.Range)
     aVal = a.Address(0, 0, xlA1, 0)
     [COLOR=#008000]'Now aVal equals "A1:B1" as a string of text
     'I want to return a variable that contains just the _
          row number of the first part of the range in the string text. _
          So I need to return the 1 in between the A and the : _
          I do this by first locating the : in the string of text.[/COLOR]
   [COLOR=#b22222][B]  strLength = Application.WorksheetFunction.Find(bVal, ":")[/B][/COLOR]
     [COLOR=#008000]'strLength should give me the position of the colon.  _
          It is located in position 3.  This code doesn't work _
          because it is not a range, it is a string.
     'I need something that can do that with a string, not a range.  It needs to be _
          dynamic because a user might have selected range AA25:BB25.  _
          This means I can't simply use left and right functions without knowing _
          first how to find the colon.[/COLOR]
End Function

 

[AlphaFrog]

Is this what you want?

[color=darkblue]Function[/color] Mjolnir(a [color=darkblue]As[/color] Excel.Range)
    Mjolnir = a(1).Row   [color=green]'Row number of 1st cell in the range[/color]
[color=darkblue]End[/color] [color=darkblue]Function[/color]

 

[WarPigl3t]

That wasn't exactly what I asked for; however, that works for my purposes. Thank you very much. It's much simpler than what I was trying to do.

 

[WarPigl3t]

does that work the same way for column?
result = b(2).column
Like that code should return column 2 for A1:B1 because B is the second column. I tried it and it isn't working.

 

[Rick Rothstein]

That wasn't exactly what I asked for; however, that works for my purposes. Thank you very much. It's much simpler than what I was trying to do.

I would use AlphaFrog's method myself, but if you wanted to see a method that works with the range's address...

Function Mjolnir(a As Excel.Range)
  Mjolnir = Val(Split(a.Address, "$")(2))
End Function

 

[AlphaFrog]

does that work the same way for column?
result = b(2).column
Like that code should return column 2 for A1:B1 because B is the second column. I tried it and it isn't working.

Function Mjolnir(a As Excel.Range)
a is the range variable. Not the cell letter.

result = a(2).column

Or
result = Range("A1:B1")(2).Column

 

[WarPigl3t]

I decided to give up on my project. There is too many logical errors. But thanks for the learning. My projects are for nothing more than the purpose to learn. I trash them once I accomplish my task and understand what I did.

 

[Rick Rothstein]

Function Mjolnir(a As Excel.Range)
a is the range variable. Not the cell letter.

result = a(2).column

Or
result = Range("A1:B1")(2).Column

And to return the column as a letter reference instead of a number reference...


First Cell

Function Mjolnir(a As Excel.Range)
  Mjolnir = Split(a.Address, "$")(1)
End Function

Last Cell

Function Mjolnir(a As Excel.Range)
  Mjolnir = Split(a.Address & a.Address, "$")(3)
End Function

 

[WarPigl3t]

When I use these...
result = a(2).column

Or
result = Range("A1:F1")(2).Column

they always return 2 as the column even though I changed the range to A1:F1.

 

[Rick Rothstein]

When I use these...
result = a(2).column

Or
result = Range("A1:F1")(2).Column

they always return 2 as the column even though I changed the range to A1:F1.

I think that statement should be written this way for what you want...

result = a(1).Offset(, a.Columns.Count - 1).Column