Check range A10 to A45 and if cell not empty then same line in B must have numeric value.
Hi.
I have tested the following macro which works to an extent.
[quote] Dim r As Range
Const MySheet = "Sheet1"
Const MyRange = "A10:B45"
Set r = Worksheets(MySheet).Range(MyRange)
If Application.WorksheetFunction.Count(r.Columns(2)) < _
Application.WorksheetFunction.CountA(r.Columns(1)) Then
MsgBox "Qty missing!", vbInformation
On Error Resume Next
Application.Goto r.Columns(2).SpecialCells(4)
On Error GoTo 0
Cancel = True
End If[quote]
However, the following happened; ranges were A10 to B20
Cells
A10=text B10=1
A11=text B11=1
A12="empty cell B12=1
A13=text B13="empty cell"
and macro has failed to pick up empty cell B13.
I must say that this has been the hardest one to get any sort of info on the internet and I have googled but I am not program minded so I might even be looking for the wrong thing.
What I am trying to do;
I have an invoice spreadsheet and B (B10 to B45) column is for QT which stands for quantity.
Column A (A10 to A45) is for item description and item is selected by a drop down list.
Columns C and D are for UOM (Unit Of Measure) and Price which are automatically fill with a vlookup formula that looks for the item selected in column A.
The macro or VB code I am after is to do the following;
Range A10 to A45, for every cell with an item or data/text if you like, then the equivalent line in column B must have quantity or in other words a numeric value.
And this is repeated throughout range A10 to A45. I was thinking of macro move on to next step when it finds an empty cell within this range but the problem is that my brother might skip a line in between and then it all goes bad once again.
One nice thing to have in place would be to have the macro popping up an inputBox when it does come across an empty cell in B column for the range in question and asks to input quantity in the cell but if I can get the first bit working as intended then I am happy.
I have tried to persuade my brother to purchase QB2013PRO, which is what I have for my small part time business and works as a treat, but hey he's just starting so I kind of understand him.
Many thanks for all the help and please lets make this work together. Well, I am not included obviously hence the reason I am here.
A very nice weekend to all.
Cheers.
Albert
all you want is a numeric value in those cells ?
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim line As Long
For line = 1 To 45
If Cells(line, 1) <> "" Then
Cells(line, 2).NumberFormat = "General"
End If
Next line
Cancel = True
End Sub
Check range A10 to A45 and if cell not empty then same line in B
Hi anglo.
My problem is a bit more than that.
I want a macro to check for cells in the range A10 to A45.
If data which will be in general format is found, or if cell is not empty then same line in column B must have a numeric value.
Example;
A10= Ascorbic Acid
Then cell B10 must have a number or must not be empty.
The process is to repeat itself throughout the range A10 to A45 which are the lines where items description will be.
The reason for the macro is so that if one item is selected and no quantity has been keyed in, then document will not be generated and saved, otherwise invoice total wont be correct.
Thanks for the input.
"Then cell B10 must have a number or must not be empty."
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim line As Long
For line = 10 To 45
If Cells(line, 1) <> "" Then
Cells(line, 2)=1234567890 '' or any other number you want **
End If
Next line
Cancel = True
End Sub
===========================================
** or number from any other source that you didnt tell us about (if another function is required to look for this number, you should write it (or tell me where that number have to come from))