Posts by Robert B

Re: Copy Over Information

Hi

and welcome to the forum

try

Dim R As Long
R = Sheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell).Row
ActiveCell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & R + 1)
ActiveCell.EntireRow.Delete

which will copy the current row to Sheet2 (you will need to amend the name accordingly)
also be careful that none of the formulae in the source sheet have set ranges (i.e. those that look like $A$1)

Hope that helps

Robert

Re: Copying Colored Cells

Hi

It souns as though there is some conditional formatting somewhere, to avoid this copy from original to target cell using Paste Special and select Formulas, or edit the conditional format on the target cell

Robert

Re: Replace Pivot Table With Formulas

Hi

I do not disagree with Dave or Windy58, but there was a newsletter (September 2007) which suggested methods for deriving statistics from a table of data which you might find answers your original question.

http://www.ozgrid.com/News/sep-2007.htm

Robert

Re: Deleting Querry Tables Only When They Are Active

Hi

and welcome to the forum

try

On Error GoTo 10
Sheets("Sheet1").Cells.Clear
Sheets("Sheet1").QueryTables.Item(1).Delete
10
End Sub

HTH

Robert

Re: IsError & Match Function In VBA Code

Hi Bob

thanks, that seems to do the trick.

Regards

Robert

Hi

I am trying and failing to use this

If (IsError(Application.WorksheetFunction.Match(StartAT, GradeArray, 0))) Then

StartAT is a text string that might be blank or a space or other invalid entry, GradeArray is a table which I am using to get a numeric value from a valid text entry. If the entry is invalid then I will use a value from an adjacent cell.

The problem is that I get the 1004 error.

Can anybody shed some light on this?

Thanks

Robert

Re: Copying Rows To Other Worksheets With Macros

Hi

try something like this

Dim N As Integer
Do Until ActiveCell = "" 'position cursor over appropriate cell 
N = ActiveCell
ActiveCell.EntireRow.Copy Destination:=Sheets(N).Range("A1")
ActiveCell.Offset(1, 0).Select
Loop

which assumes that the sheets to which you are copying the data are named "1","2","3" etc

HTH

Robert

Re: Running Code From Workbook 1 In Workbook 2

Hi

have you tried stepping through Macro 3 with either WbA or WbB as the active workbook? This may identify which line is causing the problem. The only other thing that I can think of that would produce similar symptons is that you had 2 copies of Excel running, as opening a second Excel session would not allow access to macros available to the first session.

Robert

ADDED BY ADMIN

Record A Macro.

Re: Macro To Edit Chart Elements

Hi

it seems that there might be a bit of identification missing, based purely on previous format of your code;

ActiveChart.Axes(xlCategory).Select

should there be something like

ActiveChart.Axes(xlCategory.xlSecondary).Select

HTH but apologies if I have missed your point entirely

Robert

Re: Comparing And Deleting Records

Hi

welcome to Ozgrid

assume the phone numbers are in Column C and the numbers for comparison are in Column D, selecting D2,try the following

dim myRange as Range, N as Long
set myRange = Range("C:C")
Do Until Activecell = ""
If Application.WorksheetFunction.CountIf(myRange, ActiveCell) = 1 Then
N = ActiveCell.Row
Range(("A" & N), ("C" & N)).ClearContents
End If


ActiveCell.Offset(1,0).Select
Loop


or you could use a For Each loop


HTH


Robert

Re: Save Close Reopen Workbook & Continue Running Code

Hi

welcome to the forum.

that is possible, although it does not seem to achieve a great deal, I think it might be more profitable to attempt to discover why the macro is giving an error, perhaps you could post the code that you have and maybe someone can spot the problem

Robert

ADDED BY ADMIN

[GS="Copy Method of Worksheet Class failed"]*[/GS]

Re: Hlookup Formula - Explanation?

Hi

it would appear that the element of the formula in bold is there to calculate the row number to be accessed in the reference range; in this instance 29. The seemingly superfluous comma would usually be followed by either "true" or "false" ( or 0 or 1), depending upon whether an exact match was required or the nearest match, in this instance it would default to 0

HTH

Robert

Re: Select Row Based On Text In Column And Delete All Rows Below The Selected Row

Hi
try replacing this

ActiveCell.EntireRow.Select

with

Range(ActiveCell, "A65536").EntireRow.Delete

HTH

Robert

Re: Copy Rows X Times Where Cell In Column Is Between a Numeric Range

Hi

Try the following

Dim N As Integer, myRange As Range, R As Long, I As Integer


N = Range("G3").Value
Set myRange = Range("A4", "IV8")
I = 0
Do Until I = N
R = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
myRange.Copy Destination:=Range("A" & R + 1)
I = I + 1
Loop

HTH

Robert

Re: Copy Tranpose & Reformat Based On Changes In Value

Hi

I would suggest a Pivot Table using Contract No as the Row and PMT_Period as the Column

HTH

Robert

Re: Count Longest Run Of Numbers Above Mean

Hi

this formula should help with the first part of your question

=COUNTIF(A1:A100,">"&(ROUND(AVERAGE(A1:A100),0))) amending the range, of course

HTH

Robert

Re: Macro On Dates

Hi

you can use

Range("A2")= application.worksheetfunction.weekday(Range"A1"))

HTH

Robert

Re: Cumulative Percentage Values

Hi

=G3/COUNT(B3:F3)

Robert

Re: Vba Use Variable Inside Formula

Hi and welcome

do you actually need to put a formula in the activecell; could you use something like

dim ID as long


ID = mid(thecell,3,2) ' if the ID number including prefix varies use LEN(thecell)-2 instead of 2
activecell = ID

or have I misunderstood ytour intention?

Robert

Re: Filter Information From Multiple Sheets Into Master Worksheet

Hi and welcome to the forum

As you have found, splitting a database over a number of worksheets causes limitations when you attempt to manipulate or sort data, so I would recommend putting all your member details on 1 worksheet, unless, of course, you have more than 65k members, this will enable you to apply filters in the usual way

Robert