Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Insert text when conditions are met

I need help with vba to do the following

If  Number in column C of sheet4 matches sheet19 column A
and sheet4 column AQ = "file"
and column AP of Sheet4 is not null
then place "1" in column D of sheet19
to end of row of column A of sheet19

---------------

this is what I have so far

  Dim lngRow                                            As Long
  Dim objCell                                           As Range
  Dim objRange                                          As Range
 
  On Error Resume Next
  Application.ScreenUpdating = False
 
  Set objRange = Range(Sheets("sheet4").[c3], Sheets("sheet4").Cells(Sheets("Sheet4").Cells.Rows.Count, "C").End(xlUp))
 
 If Not (objRange Is Nothing) Then
     Sheets("Missing 3PM Prices").Select
 
     For lngRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row To 2& Step -1&
 
         Set objCell = Nothing
         Set objCell = objRange.Find(What:=Cells(lngRow, "A"))
     
         If Not (objCell Is Nothing) Then
            If Not (IsEmpty(Sheets("Sheet4").Cells(objCell.Row, "AP")))    



Thanks in advance
0
leezac
Asked:
leezac
  • 2
  • 2
1 Solution
 
JPIT DirectorCommented:
Try the code attached.
Sub Data_Check()
applcation.ScreenUpdating = False
Dim lngRow As Long, sh1 As String, sh2 As String, sh3 As String
lngRow = Sheets(sh1).UsedRange.Rows.Count
sh1 = "Sheet4"
sh2 = "Sheet19"
sh3 = "Missing 3PM Prices"

If Sheets(sh1).[c3] = "" Then
    Sheets(sh3).Select
Else
    For i = lngRow To 2 Step -1
        If Sheets(sh1).Cells(i, "C").Value = Sheets(sh2).Cells(i, "A").Value _
            And Sheets(sh1).Cells(i, "AQ").Value = "file" _
            And Sheets(sh1).Cells(i, "AP").Value <> "" Then
        Sheets(sh2).Range("D" & i & ":K" & i).Value = 1 'REPLACE K WITH LAST COLUMN
    Next i
End If
End Sub

Open in new window

0
 
leezacAuthor Commented:
I modified - did not need sheet3 - posted too much information in first post.  Works except I need only Column D to be inserted with "1"
 I do not know how to change the line below
Sheets(sh2).Range("D" & ":D" & i).Value = 1
__________________________________________________________________



Application.ScreenUpdating = False
Dim lngRow As Long, sh1 As String, sh2 As String
sh1 = "Compare"
sh2 = "tmp"
 
lngRow = Sheets(sh1).UsedRange.Rows.Count


    For i = lngRow To 2 Step -1
        If Sheets(sh1).Cells(i, "C").Value = Sheets(sh2).Cells(i, "A").Value _
            And Sheets(sh1).Cells(i, "AQ").Value = "file" _
            And Sheets(sh1).Cells(i, "AP").Value <> "" Then
        Sheets(sh2).Range("D" & ":D" & i).Value = 1
 End If
Next
0
 
JPIT DirectorCommented:
Use this line
Sheets(sh2).cells(i, "D").value = 1

I thought you wanted from D to Last Column, no biggie.
0
 
leezacAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now