MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel VBA - need to copy paste conditional statement

  Asked By: Gracie    Date: Mar 04    Category: MS Office    Views: 2551

I'm a VBA newbie and would appreciate some help on putting together a
macro that will run through some employment data in column A only with
several hundred rows, find a particular location (eg. city) and
copy/paste that selection to another sheet perhaps. Any ideas?

Also, are there any good sites people can recommend where I can
download sample VBA code? I've found a few but they don't seem to hit
the mark.



2 Answers Found

Answer #1    Answered By: Deloris Harris     Answered On: Mar 04

You could try something like:

Sub FindCity()
LookFor = "Redwood City"
Set OuputRange = Range("E1")
r = 0
For Each c In Range("A1:A100")
a = InStr(c, LookFor)
If a > 0 Then
OuputRange.Offset(r, 0) = c
r = r + 1
End If
End Sub

Answer #2    Answered By: Luisa Fischer     Answered On: Mar 04

Why don't you start off by recording a macro of you doing it one time. Then
you can add a loop to it.

Here is an example where I was copying a total value out of several sheets
and putting  the value into the one, :

Sub MoveTotal()
Dim strCounter As Integer

For counter = 1 To 230
strCounter = counter
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(strCounter, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Year 2001 less McA Salaried").Select
Next counter

End Sub

Didn't find what you were looking for? Find more on Excel VBA - need to copy paste conditional statement Or get search suggestion and latest updates.