Stumped on a Macro

  • Thread starter Thread starter steveski
  • Start date Start date
S

steveski

I’m trying to create a macro that starts at a cell, double-clicks t
edit the cell, then (like pressing <enter>) moves to the next cel
below, then double-clicks to enter the cell, etc. until it reaches th
last cell with data in it.

Can you help please
 
Tell us the aim of what you are trying to achieve. It sounds like you possibly
have data in a text format that you want to be numeric and are double-clicking
each cell to do this. If this is the case, then there are much easier ways.
Either way, there may well be an easy way to accomplish whatever your goal is.
 
You are thinking in the old days of lotus. You do not have to "enter" or
"edit" a cell to change it. Example.
Tell us what you want to do.

for each c in range("a2:a200")
c.value =right(c,4)
next c
 
Sorry I did a poor job of explaining my question.

I have a date column in a data entry sheet.

When I enter a date, some formulas are automatically filled into th
cells to the right of the date (using VBA code).

However, if I copy in a column of dates, then the formulas do not ge
filled in automatically. In order to get the formulas to fill in,
must activate each cell by double-clicking on it and then pressing
So, I'd like a macro that works as follws:
1) I select the top cell in the date column.
2) I run the macro
3) The macro steps through each cell below, and activates the cell
then moves to the next cell below. (This would be the same as if
double-clicked on the cell, then presses ).
4) The macro could stop when it reaches an empty cell
 
Hi
you probably use the worksheet_change event. Though this should also be
triggered if you copy data. You may post this code so that we help you
to change this event macro rather than creating a new macro.
Probably the event macro checks if only one cell is changed and if you
copy many cells in one step the macro does not process
 
Here's my code. Sorry it's so long, I'm a rookie.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 9 Then
Target(1, 6).FormulaR1C1
"=OR(AND(MONTH(RC[-5])=RC[-4],YEAR(RC[-5])=RC[-3])," _
& "AND(MONTH(RC[-5])=RC[-2],YEAR(RC[-5])=RC[-1]))"
Target(1, 5).FormulaR1C1
"=IF(RC[-4]="""","""",IF(MONTH(TODAY())=2,YEAR(TODAY())-1,IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY()))))"
Target(1, 4).FormulaR1C1
"=IF(RC[-3]="""","""",IF(MONTH(TODAY())=2,12,IF(MONTH(TODAY())=1,11,MONTH(TODAY())-2)))"
Target(1, 3).FormulaR1C1
"=IF(RC[-2]="""","""",IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())))"
Target(1, 2).FormulaR1C1
"=IF(RC9="""","""",IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1))"
Target(1, 7).FormulaR1C1 = "=IF(RC[-6]="""",,RC[-6])"
Target(1, 20).FormulaR1C1 = "=TODAY()-RC[-19]"
Target(1, 14).FormulaR1C1
"=OR(AND(MONTH(RC[-5])=RC[-4],YEAR(RC[-5])=RC[-3])," _
& "AND(MONTH(RC[-5])=RC[-2],YEAR(RC[-5])=RC[-1]))"
ElseIf Target.Column = 17 Then
Target(1, 5).FormulaR1C1
"=IF(RC[-4]="""","""",IF(MONTH(TODAY())=2,YEAR(TODAY())-1,IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY()))))"
Target(1, 4).FormulaR1C1
"=IF(RC[-3]="""","""",IF(MONTH(TODAY())=2,12,IF(MONTH(TODAY())=1,11,MONTH(TODAY())-2)))"
Target(1, 3).FormulaR1C1
"=IF(RC[-2]="""","""",IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())))"
Target(1, 2).FormulaR1C1
"=IF(RC9="""","""",IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1))"
Target(1, 7).FormulaR1C1 = "=IF(RC[-6]="""",,RC[-6])"
Target(1, 9).FormulaR1C1
"=(RC[-8]-RC[-16])*24+((HOUR(RC[-1])-HOUR(RC[-9]))+((MINUTE(RC[-1])-MINUTE(RC[-9]))/60))"
Target(1, 10).FormulaR1C1 = "=IF(RC[-1]>=0,RC[-1]/24,"""")"
Target(1, 11).FormulaR1C1
"=IF(RC26="""","""",IF(RC26<=1,""<=1d"",IF(AND(RC26>1,RC26<=5),""2-5d"",IF(RC26>=5,"">5d""))))"
End If
End Su
 
Hi
try (not fully tested)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i
If Target.Column = 9 Then
for i = 1 to target.rows.count
Target(i, 6).FormulaR1C1 =
"=OR(AND(MONTH(RC[-5])=RC[-4],YEAR(RC[-5])=RC[-3])," _
& "AND(MONTH(RC[-5])=RC[-2],YEAR(RC[-5])=RC[-1]))"
'enter your other formulas below
next i
end if
end sub

you have to change the first parameter in your TARGET(1,n) to
TARGET(i,n)

--
Regards
Frank Kabel
Frankfurt, Germany
Here's my code. Sorry it's so long, I'm a rookie.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 9 Then
Target(1, 6).FormulaR1C1 =
"=OR(AND(MONTH(RC[-5])=RC[-4],YEAR(RC[-5])=RC[-3])," _
& "AND(MONTH(RC[-5])=RC[-2],YEAR(RC[-5])=RC[-1]))"
Target(1, 5).FormulaR1C1 =
"=IF(RC[-4]="""","""",IF(MONTH(TODAY())=2,YEAR(TODAY())-1,IF(MONTH(TODA
Y())=1,YEAR(TODAY())-1,YEAR(TODAY()))))"
Target(1, 4).FormulaR1C1 =
"=IF(RC[-3]="""","""",IF(MONTH(TODAY())=2,12,IF(MONTH(TODAY())=1,11,MON
TH(TODAY())-2)))"
Target(1, 3).FormulaR1C1 =
"=IF(RC[-2]="""","""",IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())
))"
Target(1, 2).FormulaR1C1 =
"=IF(RC9="""","""",IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1))"
Target(1, 7).FormulaR1C1 = "=IF(RC[-6]="""",,RC[-6])"
Target(1, 20).FormulaR1C1 = "=TODAY()-RC[-19]"
Target(1, 14).FormulaR1C1 =
"=OR(AND(MONTH(RC[-5])=RC[-4],YEAR(RC[-5])=RC[-3])," _
& "AND(MONTH(RC[-5])=RC[-2],YEAR(RC[-5])=RC[-1]))"
ElseIf Target.Column = 17 Then
Target(1, 5).FormulaR1C1 =
"=IF(RC[-4]="""","""",IF(MONTH(TODAY())=2,YEAR(TODAY())-1,IF(MONTH(TODA
Y())=1,YEAR(TODAY())-1,YEAR(TODAY()))))"
Target(1, 4).FormulaR1C1 =
"=IF(RC[-3]="""","""",IF(MONTH(TODAY())=2,12,IF(MONTH(TODAY())=1,11,MON
TH(TODAY())-2)))"
Target(1, 3).FormulaR1C1 =
"=IF(RC[-2]="""","""",IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())
))"
Target(1, 2).FormulaR1C1 =
"=IF(RC9="""","""",IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1))"
Target(1, 7).FormulaR1C1 = "=IF(RC[-6]="""",,RC[-6])"
Target(1, 9).FormulaR1C1 =
"=(RC[-8]-RC[-16])*24+((HOUR(RC[-1])-HOUR(RC[-9]))+((MINUTE(RC[-1])-MIN
UTE(RC[-9]))/60))"
Target(1, 10).FormulaR1C1 = "=IF(RC[-1]>=0,RC[-1]/24,"""")"
Target(1, 11).FormulaR1C1 =
Click to expand...
 
Back
Top