|
macros creating
Being new to Macros in Excel I just tried creating a simple exercise as
follows. I have stored a value in cell j6 (row 1, col 10). I wish to check the value if it is 300 and if so, store valueof 500 in cell j7(row 1, col 11). The script I entered in VB script editor. But when I executed the macro, I encountered error. defining a integer or string in VB is using DIM ctr as integer DIM flag as string. Pl let me know how to define the cells in VB script(where I want the results to be stored) i.e., Sub Macro1() ' If j6 300 Then j7 = 500 End If 'Application.CommandBars("Stop Recording").Visible = False 'Range("J8").Select 'Application.Goto Reference:="Macro1" ActiveWorkbook.Save ActiveWindow.SmallScroll Down:=-27 Range("K4").Select Application.Goto Reference:="Macro1" Application.WindowState = xlMinimized Application.Goto Reference:="Macro1" Application.Run "'dynam PROFORMA.xls'!Macro1" ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 3 Range("K6").Select ActiveCell.FormulaR1C1 = "300" Range("K6").Select ActiveCell.FormulaR1C1 = "301" Range("J6").Select ActiveCell.FormulaR1C1 = "301" Range("K6").Select ActiveCell.FormulaR1C1 = " " Range("J6").Select ActiveWorkbook.Save Range("J7").Select Application.Width = 599.25 Application.Height = 429 Application.Run "'dynam PROFORMA.xls'!Macro1" Application.CommandBars("Exit Design Mode").Visible = False Range("J6").Select ActiveWorkbook.Save ActiveWorkbook.Save End Sub Tools - Macros - -- reply to my posts are welcome Thanks |
macros creating
hi,
cells are range objects and need not be defined. instead just use the value property of the range object. to do what you want try this... Sub test1() If Range("J6").Value 300 Then Range("J7").Value = 500 End If End Sub variables need defining. same as above only different. Sub Test2() Dim jjj6 As Range 'j6 is reserved for vb/xl Dim jjj7 As Range Dim c As Long 'variable for do nothing example Set jjj6 = Cells(10, 6) Set jjj7 = Cells(10, 7) 'range("J6") is the same as Cells(10, 6) c = 1 ' do nothing example If jjj6.Value 300 Then jjj7.Value = 500 End If MsgBox "the value of c is " & c 'do nothing example End Sub for a list of data types, in vb help type Data Type then click data type summary. it will probably been greek to you but there are the types. hope this helped regards FSt1 "NSNR" wrote: Being new to Macros in Excel I just tried creating a simple exercise as follows. I have stored a value in cell j6 (row 1, col 10). I wish to check the value if it is 300 and if so, store valueof 500 in cell j7(row 1, col 11). The script I entered in VB script editor. But when I executed the macro, I encountered error. defining a integer or string in VB is using DIM ctr as integer DIM flag as string. Pl let me know how to define the cells in VB script(where I want the results to be stored) i.e., Sub Macro1() ' If j6 300 Then j7 = 500 End If 'Application.CommandBars("Stop Recording").Visible = False 'Range("J8").Select 'Application.Goto Reference:="Macro1" ActiveWorkbook.Save ActiveWindow.SmallScroll Down:=-27 Range("K4").Select Application.Goto Reference:="Macro1" Application.WindowState = xlMinimized Application.Goto Reference:="Macro1" Application.Run "'dynam PROFORMA.xls'!Macro1" ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 3 Range("K6").Select ActiveCell.FormulaR1C1 = "300" Range("K6").Select ActiveCell.FormulaR1C1 = "301" Range("J6").Select ActiveCell.FormulaR1C1 = "301" Range("K6").Select ActiveCell.FormulaR1C1 = " " Range("J6").Select ActiveWorkbook.Save Range("J7").Select Application.Width = 599.25 Application.Height = 429 Application.Run "'dynam PROFORMA.xls'!Macro1" Application.CommandBars("Exit Design Mode").Visible = False Range("J6").Select ActiveWorkbook.Save ActiveWorkbook.Save End Sub Tools - Macros - -- reply to my posts are welcome Thanks |
macros creating
Many thanks for the timely reply.
with Regards -- reply to my posts are welcome "FSt1" wrote: hi, cells are range objects and need not be defined. instead just use the value property of the range object. to do what you want try this... Sub test1() If Range("J6").Value 300 Then Range("J7").Value = 500 End If End Sub variables need defining. same as above only different. Sub Test2() Dim jjj6 As Range 'j6 is reserved for vb/xl Dim jjj7 As Range Dim c As Long 'variable for do nothing example Set jjj6 = Cells(10, 6) Set jjj7 = Cells(10, 7) 'range("J6") is the same as Cells(10, 6) c = 1 ' do nothing example If jjj6.Value 300 Then jjj7.Value = 500 End If MsgBox "the value of c is " & c 'do nothing example End Sub for a list of data types, in vb help type Data Type then click data type summary. it will probably been greek to you but there are the types. hope this helped regards FSt1 "NSNR" wrote: Being new to Macros in Excel I just tried creating a simple exercise as follows. I have stored a value in cell j6 (row 1, col 10). I wish to check the value if it is 300 and if so, store valueof 500 in cell j7(row 1, col 11). The script I entered in VB script editor. But when I executed the macro, I encountered error. defining a integer or string in VB is using DIM ctr as integer DIM flag as string. Pl let me know how to define the cells in VB script(where I want the results to be stored) i.e., Sub Macro1() ' If j6 300 Then j7 = 500 End If 'Application.CommandBars("Stop Recording").Visible = False 'Range("J8").Select 'Application.Goto Reference:="Macro1" ActiveWorkbook.Save ActiveWindow.SmallScroll Down:=-27 Range("K4").Select Application.Goto Reference:="Macro1" Application.WindowState = xlMinimized Application.Goto Reference:="Macro1" Application.Run "'dynam PROFORMA.xls'!Macro1" ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 3 Range("K6").Select ActiveCell.FormulaR1C1 = "300" Range("K6").Select ActiveCell.FormulaR1C1 = "301" Range("J6").Select ActiveCell.FormulaR1C1 = "301" Range("K6").Select ActiveCell.FormulaR1C1 = " " Range("J6").Select ActiveWorkbook.Save Range("J7").Select Application.Width = 599.25 Application.Height = 429 Application.Run "'dynam PROFORMA.xls'!Macro1" Application.CommandBars("Exit Design Mode").Visible = False Range("J6").Select ActiveWorkbook.Save ActiveWorkbook.Save End Sub Tools - Macros - -- reply to my posts are welcome Thanks |
| All times are GMT +1. The time now is 07:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com