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
<GarysStud...@discussions.microsoft.com> 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
> <GarysStud...@discussions.microsoft.com> 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 <peter...@verizonXSPAM.net> 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.
>
>