PDA

View Full Version : Solved: Counter in Range



dragos
04-02-2012, 04:13 AM
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

xld
04-02-2012, 04:25 AM
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
04-02-2012, 05:17 AM
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
04-02-2012, 10:35 PM
What does j represent?

dragos
04-02-2012, 11:43 PM
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

xld
04-03-2012, 12:42 AM
You don't use

Range(j, "B")

but

Cells(j, "B")

CatDaddy
04-03-2012, 01:27 PM
or Range("I" & j)

xld
04-03-2012, 01:31 PM
or Range("I" & j)

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

CatDaddy
04-03-2012, 01:42 PM
to each his own!

dragos
04-05-2012, 12:06 AM
It works!

Thank you xld, CatDaddy and AussieBear!