exceluser9
Active Member
- Joined
- Jun 27, 2015
- Messages
- 388
Hi,
I wanted VBA to skip the cells if it contains any value on cells 35,36 & 37 which is column AI,AJ & AK.
I have the below VBA code which will do some come validation on cells 23 which is column W and give the out put on cells 35,36 & 37. Now if there is already a Value on cells 35,36 &37 I need to VBA code to skip that populate the out put only for blank cells.
Please need amend to below VBA code
VBA code below
I wanted VBA to skip the cells if it contains any value on cells 35,36 & 37 which is column AI,AJ & AK.
I have the below VBA code which will do some come validation on cells 23 which is column W and give the out put on cells 35,36 & 37. Now if there is already a Value on cells 35,36 &37 I need to VBA code to skip that populate the out put only for blank cells.
Please need amend to below VBA code
VBA code below
Code:
Public TB As Workbook, sht As Worksheet, lr As Long, k As Long
Sub getDATA()
Set TB = ThisWorkbook
Set sht = TB.Sheets("AR")
TB.Activate
sht.Select
lr = ActiveSheet.Cells(23)(Rows.Count, 1).End(xlUp).Row
If lr = 1 Then MsgBox "Please add DATA": Exit Sub
For k = 2 To lr
Call getOf_ID_TB_WEEK(TB, sht, Cells(23)(k, 1).Value, k)
Next k
End Sub
Sub getOf_ID_TB_WEEK(wb As Workbook, sht As Worksheet, strVAL As String, indx As Long)
On Error GoTo LBLskp
Dim IDstart As Long, IDend As Long, TBstart As Long, TBend As Long
Dim Of_ID As String, TB As String, WEEK As String
Of_ID = ""
TB = ""
WEEK = ""
For i = 1 To VBA.Len(strVAL)
If VBA.Mid(strVAL, i, VBA.Len("P_ID")) = "P_ID" Then
For j = i + VBA.Len("P_ID") To i + VBA.Len("P_ID") + 20
If VBA.Mid(strVAL, j, 1) <> " " And VBA.Mid(strVAL, j, 1) <> "-" Then
IDstart = j
Exit For
End If
Next j
For j = IDstart To IDstart + 20
If VBA.Mid(strVAL, j, 1) = " " Then
IDend = j - 1
Exit For
End If
Next j
If VBA.Len(IDstart) > 0 And VBA.Len(IDend) > 0 Then Exit For
End If
Next i
Of_ID = VBA.Mid(strVAL, IDstart, IDend - IDstart + 1)
For i = IDend To VBA.Len(strVAL)
If IsNumeric(VBA.Mid(strVAL, i, 6)) = True And VBA.Len(VBA.Mid(strVAL, i, 6)) - VBA.Len(Application.WorksheetFunction.Substitute(VBA.Mid(strVAL, i, 6), " ", "")) = 0 And VBA.Len(VBA.Mid(strVAL, i, 6)) - VBA.Len(Application.WorksheetFunction.Substitute(VBA.Mid(strVAL, i, 6), "-", "")) = 0 And VBA.Len(VBA.Mid(strVAL, i, 6)) - VBA.Len(Application.WorksheetFunction.Substitute(VBA.Mid(strVAL, i, 6), " ", "")) = 0 Then
TBstart = i
Exit For
End If
Next i
For i = TBstart To VBA.Len(strVAL)
If VBA.Mid(strVAL, i, 1) = " " Then
TBend = i
Exit For
End If
Next i
TB = VBA.Mid(strVAL, TBstart, TBend - TBstart + 1)
WEEK = VBA.Right(strVAL, 6)
Cells(indx, 35).Value = Of_ID
Cells(indx, 36).Value = TB
Cells(indx, 37).Value = WEEK
Exit Sub
LBLskp:
Cells(indx, 35).Value = ""
Cells(indx, 36).Value = ""
Cells(indx, 37).Value = ""
End Sub
Last edited by a moderator: