Simple inserting column then titling

[lgjmac]

Hi,

1 how do I select the top cell of a newly inserted column (when I don't know where it is) please see code below

2 does using entirecolumn.insert change the active cell?

3 how can i insert a formula into a range of that column, up to the last data entry in the other columns?

PLEASE PLEASE PLEASE HELP, I HAVE POSTED IN SO MANY FORUMS AND HAD NO HELP!!!!

ActiveSheet.UsedRange.Find("P/L", lookat:=xlPart).EntireColumn.Insert
Selection.Cells(1, 1).Value = "Pips"
Range(Selection.Cells(2, 0), Selection.Cells(2, 0).End(xlDown)).Formula = "=IF($g2=""BUY"",$j2-$i2,$i2-$j2)"

[p45cal]

1 how do I select the top cell of a newly inserted column (when I don't know where it is) please see code belowSet xx = ActiveSheet.UsedRange.Find("P/L", lookat:=xlPart)
xx.EntireColumn.Insert
xx.Offset(, -1).EntireColumn.Cells(1).Select
'xx.Offset(, -1).EntireColumn.Cells(1).Value = "Pips"
The last, commented-out, line instead of selecting the cell, will just put the header at the top of the newly inserted column.



2 does using entirecolumn.insert change the active cell?Yes if it's to the right of the inserted column. It will have the same address though.



3 how can i insert a formula into a range of that column, up to the last data entry in the other columns?Range(xx.Offset(, -1).EntireColumn.Cells(2), Cells(Cells(Rows.Count, "B").End(xlUp).Row, xx.Offset(, -1).Column)) = "=IF($g2=""BUY"",$j2-$i2,$i2-$j2)"




PLEASE PLEASE PLEASE HELP, I HAVE POSTED IN SO MANY FORUMS AND HAD NO HELP!!!!Oh dear, see http://www.excelguru.ca/content.php?184.

[lgjmac]

1 Thank you so much. That was a really helpful response.
2 I now also understand about the importance of recognising your cross posts; great article and thank you for the direction
3 if i have a cell and want to obtain the column number to then use, I think I do this:
dim nbr as long
dim nbrx as long
set nbr = activecell.columns
set nbrx = ........ what ever ......
4 if i then want to use this in a formula say: column nbr,2 - nbrx1 , im stuck:
range(......).formula = "="nbr &"2 - "nbrx & 2""

the exact code i have is:


Dim pct As Range
Dim opn As Range
Dim pips As Long
Dim rwnbr As Long


Set newcell = ActiveSheet.UsedRange.Find("P/L", lookat:=xlPart)
Set opn = ActiveSheet.UsedRange.Find("Opened", lookat:=xlPart).Columns

If Not found Is Nothing Then
Columns("N:S").Hidden = True
newcell.EntireColumn.Insert
newcell.Offset(0, -1).Value = "Pips"
Set pips = newcell.Offset(0, -1).Columns
Range(newcell.Offset(1, -1), newcell.End(xlDown).Offset(0, -1)).Formula = "=IF($g2=""BUY"",$j2-$i2,$i2-$j2)"
newcell.End(xlToRight).Offset(0, 1).Value = "Percentage Win"
Set rwnbr = UsedRange.Cells(1, 1).End(xlDown).Rows
Set pct = newcell.End(xlToRight)
Range(pct.Offset(1, 0), pct.Offset(rwnbr - 1, 0)).Formula = "=(pips & 2)/(opn & 2)"

[p45cal]

Difficult to guess what you want from the code, but here goes:Sub ffg()
Dim pct As Range, NewCell As Range
Dim pips As Long
Dim rwnbr As Long

Set NewCell = ActiveSheet.UsedRange.Find("P/L", lookat:=xlPart)
'opn = ActiveSheet.UsedRange.Find("Opened", lookat:=xlPart).Column

If Not NewCell Is Nothing Then
Columns("N:S").Hidden = True
NewCell.EntireColumn.Insert
NewCell.Offset(0, -1).Value = "Pips"
pips = NewCell.Offset(0, -1).Column
Range(NewCell.Offset(1, -1), NewCell.End(xlDown).Offset(0, -1)).Formula = "=IF($g2=""BUY"",$j2-$i2,$i2-$j2)"
NewCell.End(xlToRight).Offset(0, 1).Value = "Percentage Win"
rwnbr = ActiveSheet.UsedRange.Cells(1, 1).End(xlDown).Row
Set pct = NewCell.End(xlToRight)
Range(pct.Offset(1, 0), pct.Offset(rwnbr - 1, 0)).FormulaR1C1 = "=RC" & pips & "/R" & rwnbr & "C1"
End If
End Sub