Target.value 255 explanation

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
Hi there!

I was given the following line as part of a sub:
Code:
FindWhat = Left(Target.Value, 255)
I need to understand what this actually does.

I was wanting a match to be found by looking through all text entries with no limit to the number of characters in the cell and I suspect the above line is incorrect.

At the moment my sheet is being pasted with rogue text entries that are all greater than 255 characters, and I think it has something to do with this line :cry:

If you need me to post more I will - hope you can help!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
The line is starting from the last column and moving left through the columns up to the Target.Value. (255 is reference to a maximum number of columns in a sheet)
 
Upvote 0
Hap said:
The line is starting from the last column and moving left through the columns up to the Target.Value. (255 is reference to a maximum number of columns in a sheet)
Sorry, but this is incorrect.
The line of code above is saying to find the first 255 characters (from the left end) in the target cell's value.

(Also, there are (currently) 256 columns available.)

I'm afraid we'll need to see more of your code (or at least I do...) to hopefully help on the rogue pasting issue.

Hope it helps.
 
Upvote 0
255 is the max column width of a cell.

See the topic Worksheet and workbook specifications in the help file.
 
Upvote 0
Guys!

Many thanks for your lightning responses!

This is the full code:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 6 Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim FindWhat$, FindWhere As Variant
FindWhat = Left(Target.Value, 255) 'THIS ROW IS THE POSSIBLE PROBLEM
Set FindWhere = _
Sheets("Training Log").Columns(7).Find(What:=FindWhat, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=True)
If FindWhere Is Nothing Then Exit Sub

Dim iIndex%
iIndex = Sheets("Training Log").Cells(FindWhere.Row, 6).Interior.ColorIndex
Target.Hyperlinks.Add _
Anchor:=Target, _
Address:="", _
SubAddress:="'Training Log'!G" & FindWhere.Row, _
TextToDisplay:="Click for Training Log Comments", _
ScreenTip:="Go to Training Log G" & FindWhere.Row
Target.Interior.ColorIndex = iIndex
With Target
.Font.FontStyle = Arial
.Font.Size = 8
.Font.Bold = True
Application.ScreenUpdating = True
End With
End Sub

What this code is doing is searching for duplicated text in column G of Training Log and Column F of the sheet the code appears in ('Analysis') creating a hyperlink to it in Column F of Analysis. What's also happening is the newly-hyperlinked cell is also shaded the same colour as column F adjacent to the duplicated text cell in Training Log e.g.

Analysis F234 duplicate text located to Training Log G20
Training Log F20 filled green
Hyperlink created in Analysis F234, cell filled green

Hope you can help - and thanks either way!
 
Upvote 0
Thank you for correcting me. I had a brain fart and was remembering a similar line of code I wrote earlier.
 
Upvote 0
Now that I made a fool of myself I have a related question. Is the function just containing the string so that it can be assigned to a cell without accidently oversizing it (since Target.Value in the example above could potentially be larger than 255 characters)?
 
Upvote 0
Hap - thanks for your help!

Well what I'm wanting Excel to do is to look through all the entries in Column G of Training Log and hyperlink to that cell from Analysis sheet if an exact match is found - I'm not sure of the details but before I added the ", 255" to that line, I was getting a Type Mismatch error.

I looked at all the occasions when I was getting this error, and it was when Excel had located the match but there were more than 255 characters in the cell.

I had thought that my code had resolved it - the macro seems to work fine, but for some reason I'm getting seemingly random entries in column G Training Log replaced by 1 of 2 entries that both contain well over 255 characters - so I'm thinking the 2 things are somehow linked...?

Hope that's a bit clearer?!
 
Upvote 0
Hap:
:biggrin: You're a good sport. I still get corrected here myself. (Thankfully, not as much as I used to, but still...) :rolleyes:
Wish I could answer your question directly but we'll need someone better than me for that. All I know is that the Find function doesn't seem to like finding strings of more than 255 characters so the limit is necessary.

zoso:
I'm using your code in a test wb and can't seem to duplicate the problem you're having. When I have it find cells with strings of more than 255 it appears to be working as desired. When you say you're "getting seemingly random entries in column G Training Log replaced by 1 of 2 entries that both contain well over 255 characters"... what is random, the cells that get replaced, or the string(s) they're getting replaced with? (Are you seeing any sort of pattern to what's happening?) - So far for me it seems to be working great.
 
Upvote 0
Hi Halface - thanks ever such a lot for your time trying to help me out!

Yes, I agree, I thought it worked brilliantly too, and it still does. I'd discovered all the pasted entries when I ran a search on a certain word which returned a load of duplicated entries, all over 255 characters.

My original question related to the meaning of the ", 255" - I wanted it to be so it didn't error when a cell containing >255 characters was found. I was thinking maybe that that part of the line had caused the phantom pasted entries, but it seems not.

You asked what is random, the cells that get replaced, or the string(s) they're getting replaced with, and am I seeing any sort of pattern to what's happening?

The cells that get replaced are either in column B or F. There is just 1 text entry being pasted in either of these columns, and it's been repeated 6 times in less than a week, but I can't see any pattern. If I'd identified that I could have been more specific of course.

Anyway, I hope that's of some use - if you like I'm happy to send you the file...

TIA!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top