Solved: Getting value from interception of row and column.

[blastpwr1970]

Hi all,

This is my first post, Help!

Am I just started getting my feet wet with VBA, please help, I don't know much about programming.

First Problem!

I need to get the value from the interception of "Look2" and "Test3" but the location of "Test3" keeps changing every day because data gets added every day by an automatic report update, also the file name changes from 5/31/2006 to 6/1/2006 has data gets updated, By the way the "Test3" name does not change nor the "Look2" name.
I need to copy these value everyday to a new workbook in a different directory.

---------------Look1 ---- Look2 ----- Look3 ------ Look4 ----- Look5
Test1
Test2
Test3 -------------------- This value
Test4
Test5


Second Problem!

I need to copy certain rows with certain text to a new sheet but only partial text is know under "Look3", the copy need to meet the condition of "TRUE" and the "partial text" for that row.

-----------Look1 ------- Look2 ------- Look3 ----------------------------------- Look4 ------- Look5
Test1
Test2 ------------------- TRUE ------- (This txt) *with this unknow*
Test3
Test4 ------------------- TRUE ------- (This txt) *with this unknow*
Test5


Any tutorial links for VBA will help a lot too.
I hope, I can also contribute to the forum in the near future.
Thank you for all your help in advance.

[Jacob Hilderbrand]

You can find the intersect value by something along these lines.


Option Explicit

Sub Macro1()

Dim Cel As Range
Dim Row As Long
Dim Col As Long

Set Cel = Range("1:1").Find(What:="Look2", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
GoTo ExitSub:
Else
Col = Cel.Column
End If
Set Cel = Range("A:A").Find(What:="Test3", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
GoTo ExitSub:
Else
Row = Cel.Row
End If

MsgBox Cells(Row, Col).Text

ExitSub:

Set Cel = Nothing

End Sub

[blastpwr1970]

Hi DRJ,

It works great, thanks for helping me.

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care

[blastpwr1970]

Hi,

By any chance does anybody have any input for the second problem?

[Jacob Hilderbrand]

In my Find code above, see the Argument LookAt:=xlWhole, change that to LookAt:=xlPart to do a partial match.

But could there be more than one match? Do you need to loop through all the matches?

[mdmackillop]

Hi Blast,
Welcome to VBAX
Re part 2, have a look at the attached sample.
Regards
MD


Option Explicit

Sub Macro1()

Dim i As Long, Txt As String
Dim Col1 As Range, Col2 As Range

Txt = "MyText"

Set Col1 = Intersect(Columns(Range("1:1").Find(What:="Look2", LookIn:=xlValues, _
LookAt:=xlWhole).Column), ActiveSheet.UsedRange)
Set Col2 = Intersect(Columns(Range("1:1").Find(What:="Test3", LookIn:=xlValues, _
LookAt:=xlWhole).Column), ActiveSheet.UsedRange)

For i = 1 To Col1.Rows.Count
If Col1(i) = True And Left(Col2(i), Len(Txt)) = Txt Then
Rows(i).Copy Sheets(2).Cells(65536, _
Col1.Column).End(xlUp).Offset(1, -Col1.Column + 1)
End If
Next

Sheets(2).Activate


End Sub

[blastpwr1970]

Thanks DRJ and Mdmackillop the code is working great.
:ole: