Thread
:
End of Page
View Single Post
#
5
Posted to microsoft.public.excel.programming
Don Guillett[_4_]
external usenet poster
Posts: 2,337
End of Page
let us know if it worked for you.
--
Don Guillett
SalesAid Software
"Ralph" wrote in message
...
DON,
NEVER MIND. I was having an attack of the Stupids.
I really appreciate your quick response, and excellent
idea.
-----Original Message-----
Don,
How would I go About using this? with a formula for
Column "B" for Example?
"='MACRO 9006 Regen Capacity Study Ver 65.xls'!
ExtractElement(RC[-1],1,""-"")"
-----Original Message-----
1.It appears that you are going to a lot of unnecessary
trouble. Try this
idea
Sub PutFormula()
Set frng = Range("b3:b" & Range("b65536").End(xlUp).Row)
With frng
.Formula = "=h7+d8"
.Formula = .Value
End With
End Sub
--
Don Guillett
SalesAid Software
"Ralph Hill" wrote in message
...
I have 2 diffrent Sub-Macro's that I run and neither
seems
to work as well as I would like. I am running a macro
multiple times (i.e., 300 times a month). In many
cases
the Text document I am Importing and formatting may be
Empty or only have 1-Line of text or 10000 Lines of
Text.
I am able to setup my columns and run all of the
formatting I want done if there are 2 or more lines of
text. However, when thre is only 1-line of text or the
document is EMPTY, the Macro gets hung up. below are
the
2 different versions I am currently using. Any and all
help would be appreicated. The Second example is
based
on
whether or not there is an "X" in column "W". The "X"
is
added with the following formula in Column-W, =IF
(A1"","X",""). This is copied to approximatley 10000
rows. This seems to work however, I am then left with
excessive amount of blank rows, resulting in an
extremely
large file due to the blank rows.
EXAMPLE 1:
'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY
AND
PASTE FORMULAS TO BOTTOM OF SHEET
Range("B2").Select
ActiveWorkbook.Names.Add Name:="RecorderFormulaB",
RefersToR1C1:= _
"=Recorders65!R2C2"
Range("B2").FormulaR1C1 = "='MACRO 9006 Regen
Capacity
Study Ver 65.xls'!ExtractElement(RC[-1],1,""-"")"
'Defines a variable called anchor cell
Application.Goto Reference:="RecorderFormulaB",
Scroll:=False
Selection.Copy
AnchorCell = ActiveCell.Offset(1, 0).Address
ActiveCell.Offset(0, -1).Select
ActiveCell.End(xlDown).Select
EndCell = ActiveCell.Offset(0, 1).Address
Range(AnchorCell, EndCell).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Goto Reference:="RecorderFormulaB",
Scroll:=False
Range("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
EXAMPLE 2:
Const SAVESTR As String = "X"
Dim myRange As Range
Dim cell As Range
Dim delRange As Range
Columns("W:W").Select
On Error Resume Next
Selection.Find(What:=SAVESTR, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False).Activate
If Err.Number < 91 And Err.Number < 0 Then
MsgBox "Unresolved Error"
Exit Sub
End If
If ActiveCell.Row 1 Then
Set myRange = Range("W1").Resize(Range( _
"W" & Rows.Count).End(xlUp).Row, 1)
For Each cell In myRange
If cell.Value < SAVESTR Then
If delRange Is Nothing Then
Set delRange = cell
Else
Set delRange = Union(delRange,
cell)
End If
End If
Next cell
If Not delRange Is Nothing Then
delRange.EntireRow.Delete
Columns("A:A").ColumnWidth = 2
Else
Columns("A:A").ColumnWidth = 20
End If
Application.ScreenUpdating = False
End Sub
.
.
Reply With Quote
Don Guillett[_4_]
View Public Profile
Find all posts by Don Guillett[_4_]