PDA

View Full Version : Solved: Worksheet_Change to concatenate values



Dave T
06-13-2013, 12:59 AM
Hello All,

This is along a similar vein to another post I had where I received great help and advice from SamT and Simon Llyod:
http://www.vbaexpress.com/forum/showthread.php?t=45622

Now I am trying to go the other way and combine individual longitude values in one cell and latitude in the next cell and have these as values in column C.

I have four worksheets in the attached workbook that might explain things better.

Basically I have tried to modify SamT's code and instead of it using column B to trigger the Worksheet_Change, I have tried to get it to use column B.

I have a UDF that works fine but I want to be able to copy the combined longitude and latitude value into Google maps and I need a value and not a formula.

Regards,
Dave T

snb
06-13-2013, 02:11 AM
Remove 'option Explicit'

Use:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then [C2:C100] = [if(A2:A100="","",if(B2:B100="","",A2:A100& ", " & B2:B100))]
End Sub

Dave T
07-11-2013, 04:25 PM
Hello snb,

Thank you very much for your solution, it does what I was after.

I have spent some time searching for a Worksheet_Change that would work on either of two options:
1) Delete values in columns B and C if the value in column A was deleted; or
2) Delete values in columns A and C if the value in column B was deleted.

During my searching I found another option that uses a different approach (albeit not a Worksheet_Change) which I modified to suit my needs.

I found several UDF examples that worked well but your solution and the one below gave me a value result (not a formula) that could be copied and entered directly into Google Maps.



Sub ConcatCols()
'http://www.vbaexpress.com/forum/showthread.php?t=41523
'concatenate columns A & B in column C
Dim LastRow As Long
With Worksheets("Sheet15")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("C2:C" & LastRow)
.Formula = "=IF(OR(A2="""",B2=""""),"""",A2 & "", "" & B2)"
.Value = .Value
End With
End With
End Sub


Once again, thank you for your help.

Regards,
Dave T

snb
07-12-2013, 01:31 AM
In that case:



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <4 Then [A2:C100] = [if(A2:A100="","",If(B2:B100="","",if(column(A2:C100)=3,A2:A100& ", " & B2:B100,A2:C100)))]
End Sub

Dave T
07-12-2013, 07:13 AM
Hello snb,

Thanks for your reply, but when I run your code I get the following message:


Run-time error '28' Out of stack space?


After this error message Excel 2007 just crashes and restarts.
Do you know why this might be happening ???

_______________________________________________________________

I have been looking at another post of mine and came up with this code, although there might be better ways:



Sub TextToColumnsAtCommaV3()
'http://www.vbaexpress.com/forum/showthread.php?t=41523
'concatenate columns A & B in column C
Dim LastRow As Long
With Worksheets("Sheet5")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B2:B" & LastRow)
.Formula = "=LEFT(A2,FIND("","",A2)-1)"
.Value = .Value
End With
With .Range("C2:C" & LastRow)
.Formula = "=0+RIGHT(A2,LEN(A2)-FIND("","",A2))"
.Value = .Value
End With
End With
End Sub


Regards,
Dave T

snb
07-12-2013, 08:05 AM
Rather obvious, I think...


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Column < 4 Then [A2:C100] = [if(A2:A100="","",If(B2:B100="","",If(column(A2:C100)=3,A2:A100& ", " & B2:B100,A2:C100)))]

Application.EnableEvents = True
End Sub

Dave T
07-12-2013, 04:00 PM
Hello snb,

My apologies as what I posted was not relevant to this post and it was referring to something else I was working on.

It was late last night when I replied and it was only after I had shut the computer down and was in bed I realised my mistake.

I was kind of hoping to be able to delete my last post but you were too quick for me.

Regards,
Dave T

Dave T
07-13-2013, 01:27 AM
Hello snb,

I have just tried to use your code and what I suspected would happen is happening...

If you already have three values in the row and one of them is deleted it deletes the other two.
The problems is when you enter data...
If either of the other two cells are already blank the Worksheet_Change determines there is a blank cell in the row and promptly deletes the value you have just entered.

Thanks for humouring me though.

Regards,
Dave T

snb
07-13-2013, 03:26 AM
Yes, conceptualizing precedes coding (at least it should....) ;);)