Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Help
Hi Everyone:
I need some help with VBA coding: Here is what I need: Search all sheets in Workbook1 in Column B. Cell A1 in "TEST SHEET" is "XXX" If Column B of All Sheets doesn't have "XXX" in it, Then copy Cell A1 of those sheets to Column A of TEST SHEET, starting with Row 2. I tried using the "<" but it doesn't do what I want it to do. It copies Everything else when i doesn't match, but I need is if "XXX" Not EXIST, just copy Cell A1 of that sheet to TEST SHEET. Hope I explain myself well. Thanks in advance. Neon520 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Help
Hi
I think this should do it: Sub Neon() Dim TargetSh As Worksheet Set TargetSh = Worksheets("TestSheet") SearchVal = TargetSh.Range("A1").Value For Each sh In ThisWorkbook.Sheets If sh.Name < TargetSh.Name Then Set f = sh.Columns("B").Find(what:=SearchVal) If f Is Nothing Then sh.Range("A1").Copy Destination:=TargetSh.Range _ ("A" & Rows.Count).End(xlUp).Offset(1, 0) End If End If Next End Sub Regards, Per "Neon520" skrev i meddelelsen ... Hi Everyone: I need some help with VBA coding: Here is what I need: Search all sheets in Workbook1 in Column B. Cell A1 in "TEST SHEET" is "XXX" If Column B of All Sheets doesn't have "XXX" in it, Then copy Cell A1 of those sheets to Column A of TEST SHEET, starting with Row 2. I tried using the "<" but it doesn't do what I want it to do. It copies Everything else when i doesn't match, but I need is if "XXX" Not EXIST, just copy Cell A1 of that sheet to TEST SHEET. Hope I explain myself well. Thanks in advance. Neon520 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Help
Sub FindMissingSheets()
Data = Sheets("TEST SHEET").Range("A1") RowCount = 2 For Each sht In Sheets If UCase(sht.Name) < "TEST SHEET" Then Set c = sht.Columns("B").Find(what:=Data, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then With Sheets("TEST SHEET") .Range("A" & RowCount) = sht.Range("A1") .Range("B" & RowCount) = sht.Name End With RowCount = RowCount + 1 End If End If Next sht "Neon520" wrote: Hi Everyone: I need some help with VBA coding: Here is what I need: Search all sheets in Workbook1 in Column B. Cell A1 in "TEST SHEET" is "XXX" If Column B of All Sheets doesn't have "XXX" in it, Then copy Cell A1 of those sheets to Column A of TEST SHEET, starting with Row 2. I tried using the "<" but it doesn't do what I want it to do. It copies Everything else when i doesn't match, but I need is if "XXX" Not EXIST, just copy Cell A1 of that sheet to TEST SHEET. Hope I explain myself well. Thanks in advance. Neon520 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix the coding? | Excel Worksheet Functions | |||
Coding | Excel Programming | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
Could someone help me with some coding | Excel Programming |