VBA code

K

kabali

Banned User
Joined
Nov 30, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a multiple sheets in the excel workbook. I need to grab cells Q13 and Q15 from each sheets then I need to a create a vba code for the following positive criteria conditions should be met if:
- Q13 is Yes, Q15 is No;
- Q13 is No, and Q15 is Yes;
- and both Q13 and Q15 are Yes.

The output should be displayed in "DestinationSheet" as Met or not met under column D by offset 1 row

I'd really appreciate it if you could help me on this
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
VBA Code: 
Sub test()
    Dim ws As Worksheet
    Dim lRow As Integer
    For Each ws In ActiveWorkbook.Worksheets
        lRow = Worksheets("DestinationSheet").Cells(Rows.Count, 4).End(xlUp).Row + 1
        With ws
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "No" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is No"
          End If
          If .Cells(13, 17).Value = "No" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is No, Q15 is Yes"
          End If
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is Yes"
          End If
        End With
    Next
End Sub
 
Upvote 0
Flashbond said:
VBA Code: 
Sub test()
    Dim ws As Worksheet
    Dim lRow As Integer
    For Each ws In ActiveWorkbook.Worksheets
        lRow = Worksheets("DestinationSheet").Cells(Rows.Count, 4).End(xlUp).Row + 1
        With ws
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "No" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is No"
          End If
          If .Cells(13, 17).Value = "No" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is No, Q15 is Yes"
          End If
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is Yes"
          End If
        End With
    Next
End Sub
[/QUOTE]
Click to expand...
For this code, I would like to add one more stuff into it. I would like to set "Yes" = 1 and "No" = 2
 
Upvote 0
Like this?
VBA Code: 
Sub test()
    Dim ws As Worksheet
    Dim lRow As Integer
    For Each ws In ActiveWorkbook.Worksheets
        lRow = Worksheets("DestinationSheet").Cells(Rows.Count, 4).End(xlUp).Row + 1
        With ws
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "No" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is No"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
          If .Cells(13, 17).Value = "No" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is No, Q15 is Yes"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is Yes"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
        End With
    Next
End Sub
 
Upvote 0
kabali said:
Hi,

I have a multiple sheets in the excel workbook. I need to grab cells Q13 and Q15 from each sheets then I need to a create a vba code for the following positive criteria conditions should be met if:
- Q13 is Yes, Q15 is No;
- Q13 is No, and Q15 is Yes;
- and both Q13 and Q15 are Yes.

The output should be displayed in "DestinationSheet" as Met or not met under column D by offset 1 row

I'd really appreciate it if you could help me on this
Click to expand...
In addition to this post, I would like to add
Flashbond said:
Like this?
VBA Code: 
Sub test()
    Dim ws As Worksheet
    Dim lRow As Integer
    For Each ws In ActiveWorkbook.Worksheets
        lRow = Worksheets("DestinationSheet").Cells(Rows.Count, 4).End(xlUp).Row + 1
        With ws
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "No" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is No"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
          If .Cells(13, 17).Value = "No" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is No, Q15 is Yes"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is Yes"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
        End With
    Next
End Sub
Click to expand...
Let me try this code first then I will let you know
 
Upvote 0
kabali said:
In addition to this post, I would like to add

Let me try this code first then I will let you know
Click to expand...
This code didn't work at all.

The output should be displayed as "Conditions met" or "Conditions not met" under D column by offset 1 row

It's like if Q13 = Yes and Q15 = No, it means condition met
else Q13 = No and Q15 = Yes, it means conditions met
else Q13 = Yes and Q15 = Yes, also conditions met

else if Q13 = No and Q15 = No, it means not met
 
Last edited:
Upvote 0
Flashbond said:
Like this?
VBA Code: 
Sub test()
    Dim ws As Worksheet
    Dim lRow As Integer
    For Each ws In ActiveWorkbook.Worksheets
        lRow = Worksheets("DestinationSheet").Cells(Rows.Count, 4).End(xlUp).Row + 1
        With ws
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "No" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is No"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
          If .Cells(13, 17).Value = "No" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is No, Q15 is Yes"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
          If .Cells(13, 17).Value = "Yes" And .Cells(15, 17).Value = "Yes" Then
            Worksheets("DestinationSheet").Cells(lRow, 4).Value = .Name & ": Q13 is Yes, Q15 is Yes"
             .Cells(13, 17).Value = 1
             .Cells(15, 17).Value = 2
          End If
        End With
    Next
End Sub
Click to expand...
This code didn't work at all.

The output should be displayed as "Conditions met" or "Conditions not met" under D column by offset 1 row

It's like if Q13 = Yes and Q15 = No, it means condition met
else Q13 = No and Q15 = Yes, it means conditions met
else Q13 = Yes and Q15 = Yes, also conditions met

else if Q13 = No and Q15 = No, it means not met
 
Upvote 0
Related post:
www.mrexcel.com

Condition if & AND

Hi, I have a multiple sheets in an excel workbook. The integers 1 and 2 have already been assigned to "Yes" and "No" respectively in each sheets. What I want to do is that to creating vba code for the following conditions if: - Q13 = yes and Q15 = No, it means, condition is met...
www.mrexcel.com www.mrexcel.com

Results in the "DestinationSheet" sheet, in column F, starting at F2 and going down.

I added the name of the sheet in column G, so you can see the result and its corresponding sheet. If you don't want the sheet name, delete this line from the macro:
VBA Code: 
 shd.Range("G" & i).Value = sh.Name

Macro:
VBA Code: 
Sub Conditions()
  Dim sh As Worksheet, shd As Worksheet
  Dim i As Long
  
  i = 2
  Set shd = Sheets("DestinationSheet")
  For Each sh In Sheets
    If sh.Name <> shd.Name Then
      If sh.Range("Q13").Value = "NO" And sh.Range("Q15").Value = "NO" Then
        shd.Range("F" & i).Value = "No met"
      Else
        shd.Range("F" & i).Value = "Met"
      End If
      shd.Range("G" & i).Value = sh.Name
      i = i + 1
    End If
  Next
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

K
  • Locked
  • Question
VBA code creation
Replies
1
Views
227
Fluff
Fluff
K
  • Locked
  • Question
Condition if & AND
Replies
4
Views
321
Fluff
Fluff
K
  • Solved
Project
Replies
4
Views
334
kabali
K
J
  • Solved
VBA or Excel – Time sheet with RT / OT based on the day of the week.
Replies
5
Views
277
awoohaw
awoohaw
I
  • Question
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
308
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,248
Messages
6,123,866
Members
449,129
Latest member
krishnamadison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top