Counter in Range

[dragos]

Dear experts,

I have the code below:

Dim r As Range
 Dim i As Integer
 i = 15
Set r = wb.Sheets("List of Products").Range("B7:B100")
        For Each cell In r
   If Not IsEmpty(cell) Then
      ' I update the current document with data from wb document
      .Range(E1 & i).Formula = wb.Worksheets("List of Products").Range("B7").Value
      Cancel = True
   End If

As you can see I am trying to increment the cell number:
Range(E & i) should be i.e. Range(E15)

I tryed using Range(cell) but did not worked.

In any other language I would of done:E(counter++)

How can I use this counter in VBA ?

Regards,

Dragos

[Bob Phillips]

Dim r As Range 
Dim i As Integer 
i = 15 
Set r = wb.Sheets("List of Products").Range("B7:B100") 
For Each cell In r 
   If Not IsEmpty(cell) Then 
      ' I update the current document with data from wb document
      .Cells(i, "E").Formula = wb.Worksheets("List of Products").Range("B7").Value 
      i = i + 1
      Cancel = True 
   End If

[dragos]

Thank you xld!!

I tryed similar for j, "B" but it throws an error:

.Cells(i, "E").Formula = wb.Worksheets("List of Products").Range(j, "B").Value

What is the trick there?

[Aussiebear]

What does j represent?

[dragos]

What does j represent?

Dim i, j as Integer

I am trying to update the date in the current file row i with date from another file row j.

Dim r As Range
        Dim i, j As Integer
        i = 15
        j = 7
Set r = wb.Sheets("List of Products").Range("B7:B100")
        For Each cell In r
   If Not IsEmpty(cell) Then
      ' I update the current document with data from wb document
      .Cells(i, "E").Formula = wb.Worksheets("List of Products").Range("B7").Value
      .Cells(i, "P").Formula = wb.Worksheets("List of Products").Range("I7").Value ' here I need (j, "I")
       i = i + 1
      j = j + 1
      Cancel = True
        End If

[Bob Phillips]

You don't use

Range(j, "B")

but

Cells(j, "B")

[CatDaddy]

or

Range("I" & j)

[Bob Phillips]

or Range("I" & j)

I don't know why, but I just can't get to like that format

[CatDaddy]

to each his own!

[dragos]

It works!

Thank you xld, CatDaddy and AussieBear!