compare and replace visio text using excel VBA

[joemo2003]

What is the format for "ws.Range().Tex t"?
I try to use the list from A1:A3, but "ws.Range("A1:A 3").Text" won't work?

[SammyB]

What is the format for "ws.Range().Tex t"?
I try to use the list from A1:A3, but "ws.Range("A1:A 3").Text" won't work?

Argggggggg, when you said "So if I enter 2, 5 in sheet1...", I thought you ment that you entered 2,5 in a single cell. How do you know how long the list is? I'm going to assume that it starts in A1, and continues on row 1, and has a blank cell at the end of the list. In other words, in the 2,5 case, A1=2, B1=5, and C1 is blank.

[SammyB]

What is the format for "ws.Range().Tex t"?
I try to use the list from A1:A3, but "ws.Range("A1:A 3").Text" won't work?

Sorry for the confusion. Does this make sense?

Sub Macro4()
	Dim wsText As Worksheet, ws As Worksheet
	Set wsText = Worksheets("TEXT")
	Set ws = Worksheets("Sheet1")
	Dim sFind As String, sReplace As String, v As Variant
	Dim i As Integer, j As Integer, iRow As Integer
	For i = 1 To wsText.Cells(1, 1).CurrentRegion.Columns.Count 'for each column
		sFind = wsText.Cells(1, i).Text
		sReplace = ""
		For j = 1 To ws.Range("A1").End(xlToRight).Column
			iRow = ws.Cells(1, j).Value
			sReplace = sReplace & wsText.Cells(iRow, i).Text & Chr(10)
		Next j
		MsgBox "Replace " & sFind & " with " & sReplace
	Next i
End Sub

[joemo2003]

oh, yeah, finally got it, thanks, I was wonder what that split function is doing here, but it make sense now.