Edgarvelez
Board Regular
- Joined
- Jun 6, 2019
- Messages
- 197
- Office Version
- 2016
- Platform
- Windows
Hi All,
I have a code that worked for me in the past on something else and tried to apply it here but it's not working and I need to modify it.
I am using Select case but that is not the problem, it's only there to select one of the two formulas that I need to use.
Basically I am entering a formula starting B7 and I need the range count of this formula based on the range of A7.
If my range count in A7 is 1 then I get the error but If my range count in A7 is 2 or more the all is good.
I have a code that worked for me in the past on something else and tried to apply it here but it's not working and I need to modify it.
I am using Select case but that is not the problem, it's only there to select one of the two formulas that I need to use.
Basically I am entering a formula starting B7 and I need the range count of this formula based on the range of A7.
If my range count in A7 is 1 then I get the error but If my range count in A7 is 2 or more the all is good.
VBA Code:
Sheets("DashBoard").Select
Range("A7").Select
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("DashBoard")
Set sh2 = Sheets("Sheet1")
sh2.AutoFilterMode = False
Application.ScreenUpdating = False
If IsEmpty(ThisWorkbook.Sheets(1).Range("B3")) Then
MsgBox ("STOP! Check or Search Type is Blank. Select Sheet Line No. or Batch No. from Drop-Down Menu in Cell B3 to Continue")
sh1.Range("D7").Select
Cancel = True
Exit Sub
End If
Application.ScreenUpdating = False
If IsEmpty(ThisWorkbook.Sheets(1).Range("A7")) Then
MsgBox ("STOP! Sheet Line No. or Batch No. to search status Is Blank starting at Cell A7. Enter Sheet Line No.'(s) or Batch No.'(s) startin at Cell A7 to Continue")
sh1.Range("D7").Select
Cancel = True
Exit Sub
End If
sh1.Range("B7").Select
Range("B7:B1048576").Select
Selection.ClearContents
sh1.Range("B7").Select
Select Case sh1.Range("B3").Value
Case "AWL Lot No."
ActiveCell.FormulaR1C1 = _
"=IF(COUNTBLANK(INDEX(Sheet1!R2C10:R1048576C13,MATCH(RC[-1],Sheet1!R2C1:R1048576C1,0),0))=4,""OK To Ship"",""Serial / Batch No. Has Been Used"")"
' Excel Sheet Formula =IF(COUNTBLANK(INDEX(Sheet1!$J$2:$M$1048576,MATCH(A7,Sheet1!$A$2:$A$1048576,0),0))=4,"OK To Ship","Serial / Batch No. Has Been Used")
Case "Serial / Batch No."
ActiveCell.FormulaR1C1 = _
"=IF(COUNTBLANK(INDEX(Sheet1!R2C10:R1048576C13,MATCH(RC[-1],Sheet1!R2C2:R1048576C2,0),0))=4,""OK To Ship"",""Serial / Batch No. Has Been Used"")"
' Excel Sheet Formula =IF(COUNTBLANK(INDEX(Sheet1!$J$2:$M$1048576,MATCH(A7,Sheet1!$B$2:$B$1048576,0),0))=4,"OK To Ship","Serial / Batch No. Has Been Used")
End Select
Set ws = ActiveSheet
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Alastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
Range("B" & Alastrow).AutoFill Destination:=Range("B" & Alastrow & ":B" & lastRow), Type:=xlFillCopy
sh1.Range("B7:B1048576").Select
Selection.Copy
With Range("B7", Range("B" & Rows.Count).End(xlUp))
.Value = .Value
End With
Application.CutCopyMode = False
sh1.Range("A7").Select
End Sub
