How to set value in Excel 1

[Rogy]

How do I set value
I have very strange problem. I don't know how to present it with words, so I write down an example.

I have 5 columns on the worksheet.
In the first column (A1) user inputs a value.
If this value is 13 character long, it should
be replaced by the value in cell E1. If not
then nothing happens.

if length(A1)=13 then
replace A1 with E1
else
leave it as it is
endif

Is this possible in Excel?

 

[TonyJollans]

Hi Rogy,

It seems like a slightly bizarre requirement that any string exactly 13 characters long should have that effect but if that is what you want, I don't think you can do it without code. If that's ok, put the following in your Worksheet_Change Event code:

If Target.Address = "$A$1" Then
    If Len(Target) = 13 Then
        Target = Cells(Target.Row, 5)
    End If
End If

Enjoy,
Tony

 

[Rogy]

Thanks Tony. Here is a star for you.
Good tip!
My function looks like this:

Sub Worksheet_Change(ByVal Target As Excel.Range)
For i = 18 To 1000
Set mc = Worksheets("Sheet1&quot.Cells(i, 2)
If Len(mc) = 13 Then
Target = Cells(Target.Row, 5)
End If
Next i
End Sub

Here is anther problem. With this function I get value from column number 5. But what if this column is in another
File?
I thought i could use VLOOKUP function. But i can't get it
right.
Example:
VLOOKUP(B18;'C:\prg\[prices.xls]prices'!$A$2:$F$2000;5;0)

Any ideas?

 

[KenWright]

Syntax looks Ok except for the fact that you are using semicolons instead of commas:-

This one works:-
=VLOOKUP(A1,'H:\Excel Workstuff\[Time Track.xls]Time Track'!$B$11:$E$15,3,0)

Regards
Ken..............

 

[Rogy]

Ken
My sytax is OK too. It works when i put the function directly in to the cell. (Semicolons are not the problem)

The problem is how to put VLOOKUP function (if possible) in to the function which Tony presented.

Lets say we have cell B18. In this I input a value. If this
value is 13 characters length, then this value should be
replaced with a value in another XLS workbook.

I hope this is not too complicated.

 

[TonyJollans]

Hi Rogy,

There are a couple of problems here.

To use VLOOKUP in VBA code (or any other worksheet function - as opposed to VBA function) you normally reference it by using Application.WorksheetFunction.Vlookup.

However, in this case it is more complicated because you are trying to reference a range in a closed workbook. For a bit of information on this, see thread707-329948 - it is not something I really know about and not very straightforward.

I have had a bit of a play with it and so far failed to make it work. If I sort it out I will post back. Or maybe someone more knowledgeable can help.

Enjoy,
Tony

 

[Rogy]

What is correct syntax of:
Application.WorksheetFunction.Vlookup

I can't get it right...

What if I had this values I am looking for in another
Worksheet not a Workbook.
Makes it easier?

 

[TonyJollans]

Hi Rogy,

Another Worksheet would make it easier, or just having the other Workbook open would do the trick. Vlookup parameters are Ranges, not strings, so if the other workbook is open this should work.

Dim wb As Workbook
Dim ws As Worksheet
Dim rr As Range
Dim rs As Range

Set wb = Workbooks("

prices.xls

")
Set ws = wb.Worksheets("

prices

")
Set rr = ws.Range("

$A$2:$F$2000

")
Set rs = Range("$B$18")
MsgBox WorksheetFunction.VLookup(rs, rr, 5, 0)

Enjoy,
Tony