| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Shortcut needed
Hi
The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
For Each cell In Range("E40:E400") '<===== change range to suit
If cell.Value 0 Then Range("N40").Value = "1" End If Next cell -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
The following DO loop will do the trick (no pun intended).
Sub CheckVals() Dim wb As Workbook Dim ws As Worksheet Dim intLoop As Integer Dim varMatch As Variant Dim intVal As Integer Set wb = ActiveWorkbook Set ws = wb.ActiveSheet Range("E40").Select Do Until intLoop = 4 intVal = ActiveCell.Offset(intLoop).Value If intVal 0 Then varMatch = 1 Else varMatch = "" End If ActiveCell.Offset(intLoop, 9).Value = varMatch intLoop = intLoop + 1 Loop Set wb = Nothing Set ws = Nothing End Sub -- Kevin Backmann "JohnUK" wrote: Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
Thanks Bob, but it wont get past the first phase.
It puts the 1 into the the first cell (being N40) but then it stops. Any ideas? "Bob Phillips" wrote: For Each cell In Range("E40:E400") '<===== change range to suit If cell.Value 0 Then Range("N40").Value = "1" End If Next cell -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E43").Select If ActiveCell 0 Then Range("N43").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
Hi Kevin,
I tried your code, but it returned a Run Time Error - Type mismatch Any ideas? John "Kevin B" wrote: The following DO loop will do the trick (no pun intended). Sub CheckVals() Dim wb As Workbook Dim ws As Worksheet Dim intLoop As Integer Dim varMatch As Variant Dim intVal As Integer Set wb = ActiveWorkbook Set ws = wb.ActiveSheet Range("E40").Select Do Until intLoop = 4 intVal = ActiveCell.Offset(intLoop).Value If intVal 0 Then varMatch = 1 Else varMatch = "" End If ActiveCell.Offset(intLoop, 9).Value = varMatch intLoop = intLoop + 1 Loop Set wb = Nothing Set ws = Nothing End Sub -- Kevin Backmann "JohnUK" wrote: Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
Try changing: Range("N40").Value = "1" to cell.offset(0,9).value = "1" Thanks Bob, but it wont get past the first phase. It puts the 1 into the the first cell (being N40) but then it stops. Any ideas? -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=545182 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
Answered your previous post:
for i = 40 to 40 + 49 if cells(i,1) 0 then cells(i,"N").Value = 1 end if Next -- Regards, Tom Ogilvy "JohnUK" wrote: Thanks Bob, but it wont get past the first phase. It puts the 1 into the the first cell (being N40) but then it stops. Any ideas? "Bob Phillips" wrote: For Each cell In Range("E40:E400") '<===== change range to suit If cell.Value 0 Then Range("N40").Value = "1" End If Next cell -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E43").Select If ActiveCell 0 Then Range("N43").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
The original one worked for me, but try this one, and if that doesn't work,
try changing the variable type for intLoop to Long: Sub CheckVals() Dim wb As Workbook Dim ws As Worksheet Dim intLoop As Integer Dim intVal As Integer Set wb = ActiveWorkbook Set ws = wb.ActiveSheet Range("E40").Select Do Until intLoop = 4 intVal = ActiveCell.Offset(intLoop).Value If intVal = 0 Then ActiveCell.Offset(intLoop, 9).Value = "" Else ActiveCell.Offset(intLoop, 9).Value = 1 End If intLoop = intLoop + 1 Loop Set wb = Nothing Set ws = Nothing End Sub -- Kevin Backmann "JohnUK" wrote: Hi Kevin, I tried your code, but it returned a Run Time Error - Type mismatch Any ideas? John "Kevin B" wrote: The following DO loop will do the trick (no pun intended). Sub CheckVals() Dim wb As Workbook Dim ws As Worksheet Dim intLoop As Integer Dim varMatch As Variant Dim intVal As Integer Set wb = ActiveWorkbook Set ws = wb.ActiveSheet Range("E40").Select Do Until intLoop = 4 intVal = ActiveCell.Offset(intLoop).Value If intVal 0 Then varMatch = 1 Else varMatch = "" End If ActiveCell.Offset(intLoop, 9).Value = varMatch intLoop = intLoop + 1 Loop Set wb = Nothing Set ws = Nothing End Sub -- Kevin Backmann "JohnUK" wrote: Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
Hi Bob,
I have got well confused over all the feedback I got over this one, but this code is the one that worked for me. Many thanks to Tom, Ikaabod, Kevin also for their help Wow I will be dishing out rewards next. Take care Regards John "Bob Phillips" wrote: For Each cell In Range("E40:E400") '<===== change range to suit If cell.Value 0 Then Range("N40").Value = "1" End If Next cell -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
I am getting tired I meant this code:
For Each cell In Range("E40:E59") If cell.Value "" Then Range("N40").Value = "1" cell.Offset(0, 7).Value = "1" End If Next cell Sorry chaps "JohnUK" wrote: Thanks Bob, but it wont get past the first phase. It puts the 1 into the the first cell (being N40) but then it stops. Any ideas? "Bob Phillips" wrote: For Each cell In Range("E40:E400") '<===== change range to suit If cell.Value 0 Then Range("N40").Value = "1" End If Next cell -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E43").Select If ActiveCell 0 Then Range("N43").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Shortcut needed
After you changed it to
For Each cell In Range("E40:E400") '<===== change range to suit If cell.Value 0 Then cell.Offset(0,9).Value = "1" End If Next cell I hope <G -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi Bob, I have got well confused over all the feedback I got over this one, but this code is the one that worked for me. Many thanks to Tom, Ikaabod, Kevin also for their help Wow I will be dishing out rewards next. Take care Regards John "Bob Phillips" wrote: For Each cell In Range("E40:E400") '<===== change range to suit If cell.Value 0 Then Range("N40").Value = "1" End If Next cell -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "JohnUK" wrote in message ... Hi The code below is my poor attempt of entering data into one cell depending on data in another cell: Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" Range("E40").Select If ActiveCell 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" and so on and so on............ What I need is a piece of code that does a loop (I guess) that does the same job with a fraction of the code. I know it can be done using formulas on the page, but I am trying to get away from that for the time being. Any help............... Regards John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| keystroke shortcut needed | Excel Discussion (Misc queries) | |||
| shortcut has change or move so this shortcut can not open | Excel Worksheet Functions | |||
| Shortcut needed | Excel Discussion (Misc queries) | |||
| keystroke shortcut needed | Excel Discussion (Misc queries) | |||
| Deleting Rows With Non-Needed Data between Needed Data | Excel Worksheet Functions | |||
Linear Mode
