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,
|
Similar Threads
