Hi All,

I have data for 100 lines in column A and column B -
Eg - Column A consists -
A
B
C
D
-
-
-
Till 100 or more lines

In Column B -

Sales
International
News
-
-
-
-
Till 100 or more lines

My query starts here -

When creating a macro to format a report I need to Call column B data
in place of column A data.

ie, where ever the column A data exists it should be replaced with
column B data.

Note - The occourence of column A data is dynamic.

Could someone help me out.

Thanks
Thyag.

This assumes that the table is in Sheet1 and the report is in Sheet2:

Sub xlator()
n = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v1 = Sheets("Sheet1").Cells(i, 1).Value
v2 = Sheets("Sheet1").Cells(i, 2).Value
For Each r In Sheets("Sheet2").UsedRange
r.Value = Replace(r.Value, v1, v2)
Next
Next
End Sub

--
Gary''s Student - gsnu2007


"Thyag" wrote:

Hi All,

I have data for 100 lines in column A and column B -
Eg - Column A consists -
A
B
C
D
-
-
-
Till 100 or more lines

In Column B -

Sales
International
News
-
-
-
-
Till 100 or more lines

My query starts here -

When creating a macro to format a report I need to Call column B data
in place of column A data.

ie, where ever the column A data exists it should be replaced with
column B data.

Note - The occourence of column A data is dynamic.

Could someone help me out.

Thanks
Thyag.

Hi Gary's Student

How do you dim the variables?

I used:

Dim n As Range
Dim i As Integer
Dim v1 As Range
Dim v2 As Range
Dim r As Range

This produced a runtime error 91; object variable or with block
variable not set at n = Sheets("Sheet1").Cells(Rows.Count,
"A").End(xlUp).Row

So a tried,

set n = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

This produced a runtime error 424, object required at set n =
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

What am I doing wrong?

Dan



So
On Oct 22, 8:59 am, Gary''s Student
wrote:
This assumes that the table is in Sheet1 and the report is in Sheet2:

Sub xlator()
n = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v1 = Sheets("Sheet1").Cells(i, 1).Value
v2 = Sheets("Sheet1").Cells(i, 2).Value
For Each r In Sheets("Sheet2").UsedRange
r.Value = Replace(r.Value, v1, v2)
Next
Next
End Sub

I'd try:

Dim n As Long 'it holds a (row) number
Dim i As Long 'it also holds a number
Dim v1 As Variant 'or string??
Dim v2 As Variant 'or String
Dim r As Range 'it's a range (a single cell) in the used range.

dan dungan wrote:

Hi Gary's Student

How do you dim the variables?

I used:

Dim n As Range
Dim i As Integer
Dim v1 As Range
Dim v2 As Range
Dim r As Range

This produced a runtime error 91; object variable or with block
variable not set at n = Sheets("Sheet1").Cells(Rows.Count,
"A").End(xlUp).Row

So a tried,

set n = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

This produced a runtime error 424, object required at set n =
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

What am I doing wrong?

Dan

So
On Oct 22, 8:59 am, Gary''s Student
wrote:
This assumes that the table is in Sheet1 and the report is in Sheet2:

Sub xlator()
n = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v1 = Sheets("Sheet1").Cells(i, 1).Value
v2 = Sheets("Sheet1").Cells(i, 2).Value
For Each r In Sheets("Sheet2").UsedRange
r.Value = Replace(r.Value, v1, v2)
Next
Next
End Sub


--

Dave Peterson

Thanks Dave!

On Oct 22, 1:42 pm, Dave Peterson wrote:
I'd try:

Dim n As Long 'it holds a (row) number
Dim i As Long 'it also holds a number
Dim v1 As Variant 'or string??
Dim v2 As Variant 'or String
Dim r As Range 'it's a range (a single cell) in the used range.