Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual to automate formula
I am looking for a way to place a formula in h and I in the corosponding row
if there is data in A. These are my two formulas =A3-WEEKDAY(A3,2)+1 =A3-DAY(A3)+1 So if there is data in A3 the first formula will be placed in H3 and the second in I3. If there is no data in A3 there will be nothing in these cells. Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual to automate formula
Sub DepositFormula()
Dim r1 As Range, r2 As Range, r3 As Range, n As Long Dim i As Long n = Cells(Rows.Count, 1).End(xlUp).Row For i = 3 To n Set r1 = Cells(i, 1) Set r2 = r1.Offset(0, 7) Set r3 = r1.Offset(0, 8) If IsEmpty(r1) Then Else r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1" r3.Formula = "=A" & i & "-DAY(A" & i & ")+1" End If Next End Sub -- Gary''s Student - gsnu200909 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual to automate formula
I took a placed the below under sheet one in the visual basic window and it
didn't seem to work. I tried this on a new book. Thank you "Gary''s Student" wrote: Sub DepositFormula() Dim r1 As Range, r2 As Range, r3 As Range, n As Long Dim i As Long n = Cells(Rows.Count, 1).End(xlUp).Row For i = 3 To n Set r1 = Cells(i, 1) Set r2 = r1.Offset(0, 7) Set r3 = r1.Offset(0, 8) If IsEmpty(r1) Then Else r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1" r3.Formula = "=A" & i & "-DAY(A" & i & ")+1" End If Next End Sub -- Gary''s Student - gsnu200909 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual to automate formula
Did you use Tools -- Macro -- Macros to try to run it? It worked for me.
However, if you want a solution that fills out the formulas when something is entered into/changes in column A, then try this in the worksheet's code module (open the workbook, select the sheet, right-click on the sheet name and choose View Code from the list, copy and paste the code below into the new module. I didn't change the other very much, so actually it's doing more work than it probably has to, but it does work. Private Sub Worksheet_Change(ByVal Target As Range) Dim r1 As Range, r2 As Range, r3 As Range, n As Long Dim i As Long If Target.Column < 1 Or Target.Columns.Count 1 Then Exit Sub End If n = Cells(Rows.Count, 1).End(xlUp).Row For i = 3 To n Set r1 = Cells(i, 1) Set r2 = r1.Offset(0, 7) Set r3 = r1.Offset(0, 8) If IsEmpty(r1) Then Else r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1" r3.Formula = "=A" & i & "-DAY(A" & i & ")+1" End If Next End Sub "Jeremy" wrote: I took a placed the below under sheet one in the visual basic window and it didn't seem to work. I tried this on a new book. Thank you "Gary''s Student" wrote: Sub DepositFormula() Dim r1 As Range, r2 As Range, r3 As Range, n As Long Dim i As Long n = Cells(Rows.Count, 1).End(xlUp).Row For i = 3 To n Set r1 = Cells(i, 1) Set r2 = r1.Offset(0, 7) Set r3 = r1.Offset(0, 8) If IsEmpty(r1) Then Else r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1" r3.Formula = "=A" & i & "-DAY(A" & i & ")+1" End If Next End Sub -- Gary''s Student - gsnu200909 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual to automate formula
Thank you very much
"JLatham" wrote: Did you use Tools -- Macro -- Macros to try to run it? It worked for me. However, if you want a solution that fills out the formulas when something is entered into/changes in column A, then try this in the worksheet's code module (open the workbook, select the sheet, right-click on the sheet name and choose View Code from the list, copy and paste the code below into the new module. I didn't change the other very much, so actually it's doing more work than it probably has to, but it does work. Private Sub Worksheet_Change(ByVal Target As Range) Dim r1 As Range, r2 As Range, r3 As Range, n As Long Dim i As Long If Target.Column < 1 Or Target.Columns.Count 1 Then Exit Sub End If n = Cells(Rows.Count, 1).End(xlUp).Row For i = 3 To n Set r1 = Cells(i, 1) Set r2 = r1.Offset(0, 7) Set r3 = r1.Offset(0, 8) If IsEmpty(r1) Then Else r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1" r3.Formula = "=A" & i & "-DAY(A" & i & ")+1" End If Next End Sub "Jeremy" wrote: I took a placed the below under sheet one in the visual basic window and it didn't seem to work. I tried this on a new book. Thank you "Gary''s Student" wrote: Sub DepositFormula() Dim r1 As Range, r2 As Range, r3 As Range, n As Long Dim i As Long n = Cells(Rows.Count, 1).End(xlUp).Row For i = 3 To n Set r1 = Cells(i, 1) Set r2 = r1.Offset(0, 7) Set r3 = r1.Offset(0, 8) If IsEmpty(r1) Then Else r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1" r3.Formula = "=A" & i & "-DAY(A" & i & ")+1" End If Next End Sub -- Gary''s Student - gsnu200909 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual to automate formula
=IF(A3="","",A3-WEEKDAY(A3,2)+1) in H3
=IF(A3="","",A3-DAY(A3)+1) in I3 -- David Biddulph "Jeremy" wrote in message ... I am looking for a way to place a formula in h and I in the corosponding row if there is data in A. These are my two formulas =A3-WEEKDAY(A3,2)+1 =A3-DAY(A3)+1 So if there is data in A3 the first formula will be placed in H3 and the second in I3. If there is no data in A3 there will be nothing in these cells. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate Formula script | Excel Discussion (Misc queries) | |||
Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
Automate a formula in excel | Excel Discussion (Misc queries) | |||
automate formula | Excel Discussion (Misc queries) | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) |