Data replacement

[Thyag]

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.

 

[Guest]

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

 

[dan dungan]

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

 

[Dave Peterson]

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]

Thanks Dave!