Hello guys, a difficult queston, in my opinion:

I'm using Excel VBA to retrieve informations from an Access Database.
As SQL's results are variable, I need to use a loop to increment data
and generate a list.

ActiveCell.CurrentRegion.Address is the command that returns me something
like "$G$5" (if one cell unmerged is selected) or "$A$2:$B$2" (if it's a
range or more than one single cell).

Basically, after opening and editing document, all these references get lost.
When I select (using still VBA) one range to use function "COUNTIF()", excel
missunderstands these ranges and my sums goes wrong between circular
references and others columns or rows.
For List generation, I'm using a for loop to increment Cells(i,j).Value.

I've created a small macro to indicate me what is the range that I'm working.
Unfortunately, when I select a cell (that is not merged), it returns me
something like "$A$25:$K$31".

What should I do?
I really appreciatte your help.

Thanks,

Post you code. It should be easily fixed.

"Thiago Labeg" wrote:

Hello guys, a difficult queston, in my opinion:

I'm using Excel VBA to retrieve informations from an Access Database.
As SQL's results are variable, I need to use a loop to increment data
and generate a list.

ActiveCell.CurrentRegion.Address is the command that returns me something
like "$G$5" (if one cell unmerged is selected) or "$A$2:$B$2" (if it's a
range or more than one single cell).

Basically, after opening and editing document, all these references get lost.
When I select (using still VBA) one range to use function "COUNTIF()", excel
missunderstands these ranges and my sums goes wrong between circular
references and others columns or rows.
For List generation, I'm using a for loop to increment Cells(i,j).Value.

I've created a small macro to indicate me what is the range that I'm working.
Unfortunately, when I select a cell (that is not merged), it returns me
something like "$A$25:$K$31".

What should I do?
I really appreciatte your help.

Thanks,

Hey Joel, I can't post my whole code, it's about 54 word pages. I'll try to
post mainly parts of it:

A part of code to fill cells (after a firts SQL query. But I have 3 SQL
working with previous results):

If rsSet1.RecordCount < 0 Then
Do While Not rsSet1.EOF
recordcnt1 = recordcnt1 + 1
j = recordcnt1 + (Val(TextBox23.Value)) + inc
Excel.ActiveSheet.Cells(j, 7).Value = "AREA " & rsSet1!area_cd
...

--------------------------
Later, for countif, I use:

Dim value1a As String
Dim value1as As String
value1a = Excel.ActiveSheet.Cells(Val(TextBox23.Value) - 1, 12).Select
celulainicial1as = ActiveCell.CurrentRegion.Address
value1as = Excel.ActiveSheet.Cells(j + 1, 12).Select
celulafinal1as = ActiveCell.CurrentRegion.Address
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"

Thanks,

"Joel" wrote:

Post you code. It should be easily fixed.

"Thiago Labeg" wrote:

Hello guys, a difficult queston, in my opinion:

I'm using Excel VBA to retrieve informations from an Access Database.
As SQL's results are variable, I need to use a loop to increment data
and generate a list.

ActiveCell.CurrentRegion.Address is the command that returns me something
like "$G$5" (if one cell unmerged is selected) or "$A$2:$B$2" (if it's a
range or more than one single cell).

Basically, after opening and editing document, all these references get lost.
When I select (using still VBA) one range to use function "COUNTIF()", excel
missunderstands these ranges and my sums goes wrong between circular
references and others columns or rows.
For List generation, I'm using a for loop to increment Cells(i,j).Value.

I've created a small macro to indicate me what is the range that I'm working.
Unfortunately, when I select a cell (that is not merged), it returns me
something like "$A$25:$K$31".

What should I do?
I really appreciatte your help.

Thanks,

Don't use currentRegion. It will give you multiple cells bounded by the
first blank cells found. It doesn't make sense for the code to look like his

A1:B10:A9:B10 which is what you were producing.


Dim value1a As Range
Dim value1as As SRange
Dim RowNum as long
RowNum = Val(TextBox23.Value)
set value1a = Excel.ActiveSheet.Cells(RowNum - 1, 12)
celulainicial1as = value1a.Address
set value1as = Excel.ActiveSheet.Cells(j + 1, 12)
celulafinal1as = value1as.Address
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"


"Thiago Labeg" wrote:

Hey Joel, I can't post my whole code, it's about 54 word pages. I'll try to
post mainly parts of it:

A part of code to fill cells (after a firts SQL query. But I have 3 SQL
working with previous results):

If rsSet1.RecordCount < 0 Then
Do While Not rsSet1.EOF
recordcnt1 = recordcnt1 + 1
j = recordcnt1 + (Val(TextBox23.Value)) + inc
Excel.ActiveSheet.Cells(j, 7).Value = "AREA " & rsSet1!area_cd
...

--------------------------
Later, for countif, I use:

Dim value1a As String
Dim value1as As String
value1a = Excel.ActiveSheet.Cells(Val(TextBox23.Value) - 1, 12).Select
celulainicial1as = ActiveCell.CurrentRegion.Address
value1as = Excel.ActiveSheet.Cells(j + 1, 12).Select
celulafinal1as = ActiveCell.CurrentRegion.Address
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"

Thanks,

"Joel" wrote:

Post you code. It should be easily fixed.

"Thiago Labeg" wrote:

Hello guys, a difficult queston, in my opinion:

I'm using Excel VBA to retrieve informations from an Access Database.
As SQL's results are variable, I need to use a loop to increment data
and generate a list.

ActiveCell.CurrentRegion.Address is the command that returns me something
like "$G$5" (if one cell unmerged is selected) or "$A$2:$B$2" (if it's a
range or more than one single cell).

Basically, after opening and editing document, all these references get lost.
When I select (using still VBA) one range to use function "COUNTIF()", excel
missunderstands these ranges and my sums goes wrong between circular
references and others columns or rows.
For List generation, I'm using a for loop to increment Cells(i,j).Value.

I've created a small macro to indicate me what is the range that I'm working.
Unfortunately, when I select a cell (that is not merged), it returns me
something like "$A$25:$K$31".

What should I do?
I really appreciatte your help.

Thanks,

Ok!
good point Joel,

So what else can I do?
I need to retrieve data in this format "$A$3", "$A$300" to use in Countif and
to adjust page breaks.



"Joel" wrote:

Don't use currentRegion. It will give you multiple cells bounded by the
first blank cells found. It doesn't make sense for the code to look like his

A1:B10:A9:B10 which is what you were producing.


Dim value1a As Range
Dim value1as As SRange
Dim RowNum as long
RowNum = Val(TextBox23.Value)
set value1a = Excel.ActiveSheet.Cells(RowNum - 1, 12)
celulainicial1as = value1a.Address
set value1as = Excel.ActiveSheet.Cells(j + 1, 12)
celulafinal1as = value1as.Address
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"


"Thiago Labeg" wrote:

Hey Joel, I can't post my whole code, it's about 54 word pages. I'll try to
post mainly parts of it:

A part of code to fill cells (after a firts SQL query. But I have 3 SQL
working with previous results):

If rsSet1.RecordCount < 0 Then
Do While Not rsSet1.EOF
recordcnt1 = recordcnt1 + 1
j = recordcnt1 + (Val(TextBox23.Value)) + inc
Excel.ActiveSheet.Cells(j, 7).Value = "AREA " & rsSet1!area_cd
...

--------------------------
Later, for countif, I use:

Dim value1a As String
Dim value1as As String
value1a = Excel.ActiveSheet.Cells(Val(TextBox23.Value) - 1, 12).Select
celulainicial1as = ActiveCell.CurrentRegion.Address
value1as = Excel.ActiveSheet.Cells(j + 1, 12).Select
celulafinal1as = ActiveCell.CurrentRegion.Address
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"

Thanks,

"Joel" wrote:

Post you code. It should be easily fixed.

"Thiago Labeg" wrote:

Hello guys, a difficult queston, in my opinion:

I'm using Excel VBA to retrieve informations from an Access Database.
As SQL's results are variable, I need to use a loop to increment data
and generate a list.

ActiveCell.CurrentRegion.Address is the command that returns me something
like "$G$5" (if one cell unmerged is selected) or "$A$2:$B$2" (if it's a
range or more than one single cell).

Basically, after opening and editing document, all these references get lost.
When I select (using still VBA) one range to use function "COUNTIF()", excel
missunderstands these ranges and my sums goes wrong between circular
references and others columns or rows.
For List generation, I'm using a for loop to increment Cells(i,j).Value.

I've created a small macro to indicate me what is the range that I'm working.
Unfortunately, when I select a cell (that is not merged), it returns me
something like "$A$25:$K$31".

What should I do?
I really appreciatte your help.

Thanks,

I assumed celulainicial1as will equal $A$3 and
I assumed celulafinal1as will equal $A$300

The defult condition for the address property is RowAbsolute:=True, and
ColumnAbsolute:=True

You could use value1a.Address(RowAbsolute:=True,ColumnAbsolute:= True)


The code should work as I posted. Notice I replace 12 with "L".

set value1a = Excel.ActiveSheet.Cells(RowNum - 1, "L")
celulainicial1as = value1a.Address
set value1as = Excel.ActiveSheet.Cells(j + 1, "L")
celulafinal1as = value1as.Address

The countif combines the two addresses and put a colon between the addrress.

Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"

Another method would to do it like this

Set FormulaRange = Range(value1a,value1as)
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
FormulaRange.address & ",""x"")"


"Thiago Labeg" wrote:

Ok!
good point Joel,

So what else can I do?
I need to retrieve data in this format "$A$3", "$A$300" to use in Countif and
to adjust page breaks.



"Joel" wrote:

Don't use currentRegion. It will give you multiple cells bounded by the
first blank cells found. It doesn't make sense for the code to look like his

A1:B10:A9:B10 which is what you were producing.


Dim value1a As Range
Dim value1as As SRange
Dim RowNum as long
RowNum = Val(TextBox23.Value)
set value1a = Excel.ActiveSheet.Cells(RowNum - 1, 12)
celulainicial1as = value1a.Address
set value1as = Excel.ActiveSheet.Cells(j + 1, 12)
celulafinal1as = value1as.Address
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"


"Thiago Labeg" wrote:

Hey Joel, I can't post my whole code, it's about 54 word pages. I'll try to
post mainly parts of it:

A part of code to fill cells (after a firts SQL query. But I have 3 SQL
working with previous results):

If rsSet1.RecordCount < 0 Then
Do While Not rsSet1.EOF
recordcnt1 = recordcnt1 + 1
j = recordcnt1 + (Val(TextBox23.Value)) + inc
Excel.ActiveSheet.Cells(j, 7).Value = "AREA " & rsSet1!area_cd
...

--------------------------
Later, for countif, I use:

Dim value1a As String
Dim value1as As String
value1a = Excel.ActiveSheet.Cells(Val(TextBox23.Value) - 1, 12).Select
celulainicial1as = ActiveCell.CurrentRegion.Address
value1as = Excel.ActiveSheet.Cells(j + 1, 12).Select
celulafinal1as = ActiveCell.CurrentRegion.Address
Excel.ActiveSheet.Cells(j + 6, 13).Formula = "=COUNTIF(" &
celulainicial1as & ":" & celulafinal1as & ",""x"")"

Thanks,

"Joel" wrote:

Post you code. It should be easily fixed.

"Thiago Labeg" wrote:

Hello guys, a difficult queston, in my opinion:

I'm using Excel VBA to retrieve informations from an Access Database.
As SQL's results are variable, I need to use a loop to increment data
and generate a list.

ActiveCell.CurrentRegion.Address is the command that returns me something
like "$G$5" (if one cell unmerged is selected) or "$A$2:$B$2" (if it's a
range or more than one single cell).

Basically, after opening and editing document, all these references get lost.
When I select (using still VBA) one range to use function "COUNTIF()", excel
missunderstands these ranges and my sums goes wrong between circular
references and others columns or rows.
For List generation, I'm using a for loop to increment Cells(i,j).Value.

I've created a small macro to indicate me what is the range that I'm working.
Unfortunately, when I select a cell (that is not merged), it returns me
something like "$A$25:$K$31".

What should I do?
I really appreciatte your help.

Thanks,

Thank you very much Joel!
Great idea, great examples and explanation.

Bye,