intersecting cells 1

[Luis939]

How do you find the intersecting cell between 2 other cells, i want to find a word like total 1, store only its column, and then find another word like total 2, and store only its row, and use that info to find their intersection so that i can select it and input copy its contents, thanks

 

[SkipVought]

Hi,

Use the Intersect function...

    Dim rng As Range
    Set rng = Application.Intersect(rngRow, rngCol)
    If Not rng Is Nothing Then
        'Houston, we have an intersection
        
    End If

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Here is one way to do what you want:

Option Explicit

Sub SelectIntersection()
Dim nColumn As Integer
Dim nRow As Long
  
  With ActiveSheet.UsedRange
    nRow = .Find(What:="total 2", LookIn:=xlValues, Lookat:=xlWhole).Row
    nColumn = .Find(What:="total 1", LookIn:=xlValues, Lookat:=xlWhole).Column
  End With
    Cells(nRow, nColumn).Select
  
End Sub

 

[SkipVought]

Actually there's a simpler solution to YOUR scenerio...

YourValue = Cells(TheRow, TheColumn).Value

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

is there anyway to affect a cell without selecting it, like i want to take whatever is in my active cell and put it in the cell next to it, but i dont want to select it, i want to stay in the same range

 

[Zathras]

Here is one way:

Sub CopyToRight()
  ActiveCell.Offset(0, 1) = ActiveCell.Value
End Sub

or

Sub CopyToLeft()
  ActiveCell.Offset(0, -1) = ActiveCell.Value
End Sub

Of course, if there is a chance that you might try to copy to the left from column "A" or copy to the right from column "IV" then you would need to put in some error trapping.

 

[Luis939]

Just to follow up, i want to take the following instruction

Left(Range(activecell).Text, InStr(Range _(activecell).Text, ",&quot - 1)

meaning i extract a string from the active cell.

but is there any way i can say, take this string and put it in cell A1, and also if i want, can i put that string to a cell to the left of it ( ActiveCell.Offset(0, -1).Range("A1&quot.Select
even though these destination cells arent selected becuase the activecell is selected, thanks

 

[Luis939]

sorry there is no underscore between range and (activecell)

 

[xlbo]

I personalloy like using the WITH statement to implicitly enter data etc

With Activecell
range("A1&quot.value = .value
'will put activecell value into A1
end with

with activecell
.offset(0,-1).value = .value
'will enter activcell.value into the cell to the left of the active cell
end with Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Luis939]

im still having trouble with this function
Left(Range(ActiveCell).Text, InStr(Range(ActiveCell).Text, ",&quot - 1)

i want to go to any cell i want and use that function (thats why i have activecell as the cell to be used in the function) but i want to take it and put it anywhere i want, either a specific cell such as A1 or any cell relative to the acctive cell, but i keep getting a range of object global failed

i tried
Left(Range(ActiveCell).Text, InStr(Range(ActiveCell).Text, ",&quot - 1) = ActiveCell.Offset(0, -1).Range("A1&quot.Select

and also

ActiveCell.Offset(0, -1).Range("A1&quot.Select =
Left(Range(ActiveCell).Text, InStr(Range(ActiveCell).Text, ",&quot - 1)

I keep gettin the same error, maybe you could work your code with this I'm not sure, I guess Im still learning VB and im just wrong with my syntax, can you help thanks

 

[xlbo]

i tried
Left(Range(ActiveCell).Text, InStr(Range(ActiveCell).Text, ",&quot - 1) = ActiveCell.Offset(0, -1).Range("A1&quot.Select

You don't need the select and you have this the wrong way round

ActiveCell.Offset(0, -1).value = Left(ActiveCell.Text, InStr(ActiveCell.Text, ",&quot - 1)

This will put the left side of the activecell text up to a "," into the cell to the left of the activecell Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Luis939]

THANKS ALOT FOR THE HELP!!! but sorry, not to be a pest, but what if i want to put the contents of the cell in another worksheet, mmm is that possible

 

[xlbo]

Try this:
sheets("Sheet2&quot.range(ActiveCell.Offset(0, -1).address).value = Left(ActiveCell.Text, InStr(ActiveCell.Text, ",&quot - 1)

This will put the value in the active cell into the the cell to the left of the active cell address on sheet2
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde