Posts by peach

Thanks Andy! (",)

ThisWorkbook.Sheets(1).Range(A2) =
.Cells.Find(What:="findme", After:=.Cells(1, 1),
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)(2, 1).Value

The code above returns the value below the cell containing "findme", what shld I do if i want to look for the value 2 cells below the value?

How to I get the co-ordinates value of a cell.. eg if the cell is AB1 I only wan to extract "AB" how do it?

Thks.

:wink1:

heee.. it works!
thks.

Hi,

The following finds a string "AA" in the first column of a worksheet. What if I wanna search the whole sheet for the "AA" String how do I do it?

ThisWorkbook.Sheets(1).Range(x,y) = _
.Columns(1).Find(What:="AA", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)(1, 1)).Value
Next

u're sharp!

Thank you.:wink2:

Thks Dave. I am using the microsoft VB in excel and my help files are not installed.
=(

THks alot.

Hi Dave,

I only got the values for Chairs but not tables. Why is it so?

I selected the heading in book1 and click the button.

Hi,

I have 2 workbooks. There's a button in book1 which on click I want it to get the total values in book2.
As the number of items and the position of each item in the cell is not fix, I want to do a check on both the column and row for the pair(chair,total) and (table,total)

How do to it? I am a novice in VBA. pls help

Can anyone enlighten me on the syntax of the Find function?

ThisWorkbook.Sheets(1).Range("BN4") = _
.Columns(1).Find(What:="Total", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)(1, 2).Value

erm...

My target.xls will take values from 10 other copy files in different directory.

eg.

c:\mywork\target.xls
c:\myinfo\copy1.xls
c:\myinfo\copy2.xls
c:\myinfo\copy3.xls

Can I don't open all the copy.xls files instead I will just supply the directory of the files?

Thanks alot! This is what I wanted.

Can I provide a file dir together with the file name as i have abt 10 copy files?

This whole line was highlighted.

ThisWorkbooks.Sheets(1).Range("A1") = _
.Columns(1).Find(What:="Total", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)(1, 2).Value

My "Total" is in sheet1 of target.xls.
I didn't change anything, I reused the excel sheet you gave me and I added another button in copy.xls with the collowing code

Private Sub CommandButton1_Click()
With Workbooks("target.xls").Sheets(1)
ThisWorkbooks.Sheets(1).Range("A1") = _
.Columns(1).Find(What:="Total", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)(1, 2).Value
End With

End Sub

I tried the other way round, instead of putting the button in target.xls I need it to be in copy.xls but it dun work

Sub TryMe()
With Workbooks("target.xls").Sheets(1)
ThisWorkbooks.Sheets(1).Range("A1") = _
.Columns(1).Find(What:="Total", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)(1, 2).Value
End With
End Sub

Hi Dave,

Can you attach the target.xls as well?
thks

Hi Dave,

VBscript is alien to me.. I know only the basic... in your code.

"ThisWorkbook" what does it do? care to enlighten me? I am trying out your code but it doesnt seems to work.

Hi,

I want to copy the value of another excel sheet(copy.xls) in the target excel sheet(target.xls) on the click of a button in target.xls.

A search is required on the "worksheetA" of copy.xls to take the value of the cell next to the cell containing the string "Total". Eg

Total | 12

how do I do it?

Hi,

I am using the "paste-link" function in excel to reference my cell in workbookA to another cell in workbookB.

Example,
my link is =[workbookB.xls]sheet1!$A$1

My whole excel is full of links to workbookB, if I want to change half of my links to reference to workbook C.

What is a quick way to do it?

thks

:dead:

I have reports to file every month from different divisions.

Each division have the same template.

I need to extract values from each division workbook and combine into my workbook. I have used the paste link method to do the extract and pasting of values.

As each time the file name for a particular division might be different. Other than renaming the file is there any other method to do it?

Can I key in the file name in the excel cell so that my division file name can be dynamic?

pls help.

Thank you Dave. :flower: