how to create a range with fixed columns and changing rows
hi i am new to this forum, i am also new to VBA in excel. i have been able to do some scripting for my solution where i can select a number for employees and the number is transferred to another sheet and populated the respectibe number of rows = number of employess, i will be send the sheet to the number of employees who will be populating the sheet, i have created a button on the sheet which will run a macro to find empty cell in mandatory columns and is the value is empty need to send a message as "incomplete fields", i am not able to identify how i should go about it should i search by range or each column??
some advice on this would be greatful.
Hi shettyp,
Welcome to excel forum.
Can you attach a sample workbook?
regards pike
If the solution helped please donate here to the RSPCA
Sites worth visiting;
J&R Solutions - royUK
AJP Excel Information - Andy Pope
Spreadsheet Toolbox
VBA for smarties - snb
i have been able to write the code, for some reason it does not allow me to process the If loop, i have no idea how to fix it, the code i have is below
Sub Find_Empty_Cell() Dim myRange, rngC, rngE, rngG, rngH, rngN, rngO, rngR, rngU, rngV, rngW, rngX, rngY, rngAN, rngAO, rngAP As Range 'Dim myRowValue, myRow As Long Dim myRowValue As Long Dim myRow As Integer Dim i As Integer Dim iColNo3, iColNo5, iColNo7, iColNo8, iColNo14, iColNo15, iColNo18, iColNo21, iColNo22, iColNo23, iColNo24, iColNo25, iColNo41, iColNo42, iColNo43 As Variant ActiveWorkbook.Sheets("Menu").Activate Range("R14:C8").Select myRowValue = Cells(14, "H").Value myRow = 7 + myRowValue iColNo3 = 3 iColNo5 = 5 iColNo7 = 7 iColNo8 = 8 iColNo14 = 14 iColNo15 = 15 iColNo18 = 18 iColNo21 = 21 iColNo22 = 22 iColNo23 = 23 iColNo24 = 24 iColNo25 = 25 iColNo41 = 41 iColNo42 = 42 iColNo43 = 43 Set rngC = Sheets("Employee Details - Standard").Range("C" & iColNo3) Set rngE = Sheets("Employee Details - Standard").Range("E" & iColNo5) Set rngG = Sheets("Employee Details - Standard").Range("G" & iColNo7) Set rngH = Sheets("Employee Details - Standard").Range("H" & iColNo8) Set rngN = Sheets("Employee Details - Standard").Range("N" & iColNo14) Set rngO = Sheets("Employee Details - Standard").Range("O" & iColNo15) Set rngR = Sheets("Employee Details - Standard").Range("R" & iColNo18) Set rngU = Sheets("Employee Details - Standard").Range("U" & iColNo21) Set rngV = Sheets("Employee Details - Standard").Range("V" & iColNo22) Set rngW = Sheets("Employee Details - Standard").Range("W" & iColNo23) Set rngX = Sheets("Employee Details - Standard").Range("X" & iColNo24) Set rngY = Sheets("Employee Details - Standard").Range("Y" & iColNo25) Set rngAN = Sheets("Employee Details - Standard").Range("AN" & iColNo41) Set rngAO = Sheets("Employee Details - Standard").Range("AO" & iColNo42) Set rngAP = Sheets("Employee Details - Standard").Range("AP" & iColNo43) ActiveWorkbook.Sheets("Employee Details - Standard").Activate For i = 8 To myRow If ActiveSheet.Range("rngC").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngE").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngG").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngH").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngN").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngO").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngR").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngU").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngV").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngW").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngX").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngY").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngAN").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngAO").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If If ActiveSheet.Range("RngAP").Cells(i, 1).Value = "" Then ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 3 ActiveCell.Text = "INCOMPLETE" Exit Sub End If Next i ActiveWorkbook.Sheets("Menu").Activate Cells(6, 12).Select ActiveCell.Interior.ColorIndex = 10 ActiveCell.Text = "COMPLETE" Cells(6, 12).Font.Bold = True End Sub
Last edited by shettyp; 01-17-2012 at 06:45 AM. Reason: code included as per forum rules
hi Pike, i have changed the approach for the solution and have a updated code but i am not sure if the code is working, it does not give me any error, i am not sure if the code is looking for each row for empty cellsSub Complete_1() Dim myRange As Range 'Dim myRowValue, myRow As Long Dim myRowValue As Long Dim myRow As Integer Dim FinalRow As Long Dim i As Long ' Dim iColNo3, iColNo5, iColNo7, iColNo8, iColNo14, iColNo15, iColNo18, iColNo21, iColNo22, iColNo23, iColNo24, iColNo25, iColNo41, iColNo42, iColNo43 As Variant ActiveWorkbook.Sheets("Menu").Activate Range("I12").Select 'Msg for zero entered in the number of Employees If ActiveCell.Value = 0 Then MsgBox "Please Enter a Value Greater than 0" End If 'select the Employee numbers myRowValue = Cells(12, 9).Value 'Select the range with Employees myRow = 7 + myRowValue ActiveWorkbook.Sheets("Employee Details - Standard").Activate 'Select the Mandatory columns Set myRange = Application.Union(Columns(3), Columns(5), Columns(7), Columns(8), Columns(14), Columns(15), Columns(18), Columns(21), Columns(22), Columns(23), Columns(24), Columns(25), Columns(41), Columns(42), Columns(43)) 'Find the last non blank row based on mandatory field column B FinalRow = Range("B65536").End(xlUp).Row - 7 'Search for blanks in each row for the select columns For i = 8 To FinalRow If ActiveCell.Value = Empty Then ActiveWorkbook.Sheets("Menu").Activate ActiveSheet.Select Cells(12, 3).Select ActiveCell.Interior.ColorIndex = 3 'change the colour to Red ActiveCell.Value = "INCOMPLETE" ' Add text to the status Exit Sub End If Next i ActiveWorkbook.Sheets("Menu").Activate Cells(12, 3).Select ActiveCell.Interior.ColorIndex = 10 ' Change the colour to Green ActiveCell.Value = "COMPLETE" 'Add text to the cell Cells(12, 3).Font.Bold = True End Sub
Hi shettyp
this reads ok ,,but give it a test
Sub Complete_1() Dim myRange As Range 'Dim myRowValue, myRow As Long Dim myRowValue As Long Dim myRow As Integer Dim FinalRow As Long Dim i As Long Application.ScreenUpdating = False With ActiveWorkbook.Sheets("Menu") InputData: If .Range("I12").Value <= 0 Then myRowValue = InputBox("Please Enter a Value Greater than 0", "Data Entry") If myRowValue > 0 Then .Range("I12").Value = myRowValue Else GoTo InputData: End If End If myRow = 7 + myRowValue With ActiveWorkbook.Sheets("Employee Details - Standard") myColumns = VBA.Array(3, 5, 7, 8, 14, 15, 18, 21, 22, 23, 24, 25, 41, 42, 43) FinalRow = .Cells(Rows.Count, "A").End(xlUp).Row - 7 For i = 8 To FinalRow For Each ii In myColumns If .Cells(i, ii).Value = Empty Then With ActiveWorkbook.Sheets("Menu").Cells(12, 3) .Interior.ColorIndex = 3 .Value = "INCOMPLETE" End With Application.ScreenUpdating = True Exit Sub End If Next ii Next i End With With .Cells(12, 3) .Interior.ColorIndex = 10 .Value = "COMPLETE" .Font.Bold = True End With End With Application.ScreenUpdating = True End Sub
regards pike
If the solution helped please donate here to the RSPCA
Sites worth visiting;
J&R Solutions - royUK
AJP Excel Information - Andy Pope
Spreadsheet Toolbox
VBA for smarties - snb
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks