Simple loop/resetting variable help - Xtreme Visual Basic Talk

06-22-2012, 12:03 PM

smasood

Newcomer

Join Date: Jun 2012

Posts: 8

Simple loop/resetting variable help

Hi guys,
What I am trying to do is to search sheet1 for every value in column A of sheet2 and once found in sheet1, get the value of column C in sheet1 and put it in sheet2. The only problem I am having is that the variables don't reset. I mean that when it finds the Range of the first value, it stores that value till the program is done looping and doesn't proceed with the for loop.

This is what I have:

Code:

Sub Reset_Used_Range()
a = ActiveSheet.UsedRange.Rows.Count
End Sub

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'


Dim sheet1 As Worksheet
 Dim diffSheet As Worksheet
 Dim LastRow As Integer
 Dim found As Boolean
 

 Set sheet1 = ThisWorkbook.Sheets(1)
 Set diffSheet = ThisWorkbook.Sheets(2)
 
 With sheet1
 diffSheet.Activate
 LastRow = diffSheet.Cells(.Rows.Count, 2).End(xlUp).Row
 sheet1.Activate
 
 
 For rwn = 1 To LastRow
 
FindFTP = diffSheet.Cells(rwn, 1)
 
  

    Dim strTOFIND As String
    strTOFIND = FindFTP
    'MsgBox (strTOFIND)
    Dim rngFound As Range
    Dim rngToDelete As Range
    Dim strFirstAddress As String
    Application.ScreenUpdating = False
    With sheet1.Range("D:D")
        Set rngFound = .Find(What:=FindFTP, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            Lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
                             
            
              'MsgBox (findClinic)
    If rngFound Is Nothing Then
     diffSheet.Cells(rwn, 3).FormulaR1C1 = ""
     Application.ActiveSheet.UsedRange
     Call Reset_Used_Range

     Else
     
            findClinic = sheet1.Cells(ActiveCell.Row, 3)
            
            diffSheet.Cells(rwn, 3).FormulaR1C1 = findClinic
              
                 Application.ScreenUpdating = True
                 Application.ActiveSheet.UsedRange
                 Call Reset_Used_Range
                 findClinic = Null

              End If
    End With
    Next rwn
    End With
  
  

End Sub

06-23-2012, 06:18 PM

ZKat

Centurion

Join Date: May 2006

Posts: 132

Hi,

I am a little confused on this one. I have not tested the code, but scanning through, it appears that some of your variables are not declared. Do you have Option Explicit at the top, if not add to your code.

Maybe you have other code with all the variables declared?

Last edited by ZKat; 06-24-2012 at 03:21 PM.

06-26-2012, 06:26 AM

TheRealTinTin TheRealTinTin is offline

Junior Contributor

Join Date: Nov 2008

Location: Glasgow, UK

Posts: 328

By the sounds of it, there will be only 1 match? If this is true then you could simply use a lookup formula rather than VBA.

__________________
Artificial Intelligence is no match for natural stupidity

07-01-2012, 08:22 AM

cacody

Freshman

Join Date: Apr 2011

Location: Scottsdale AZ

Posts: 37

Smasood
I'm learning VBA also but through trial and error I built this macro that I think does what you want:

Sub copydata()
' This version selects all values in range in column A, and copies data in column C to different sheet with formatting
'
Dim cell As Range
Application.ScreenUpdating = False
'Name = InputBox("Select name to copy: ", Title:="Select Name") 'This will allow you to specify a single value
Range("a1:a10").Select 'You specify the range here
For Each cell In Selection
'If cell(1, 1).Value = Name Then 'Use this to select a variable Name
If cell(1, 1).Value <> "" Then 'Use this to select all values in column A
cell(1, 3).Select
If nextrow = 0 Then
nextrow = Sheets("Sheet2").Range("a30").End(xlUp).Row 'Make sure the range is below the number of values to be copied
Else
nextrow = Sheets("Sheet2").Range("a30").End(xlUp).Row + 1
End If
Selection.Copy Sheets("Sheet2").Cells(nextrow, 1)
End If
Next cell
End Sub

07-03-2012, 01:31 PM

MPi

Contributor

Forum Leader
* Expert *

Join Date: Dec 2001

Location: Quebec

Posts: 707

Hello,

In your code, I think that
findClinic = sheet1.Cells(ActiveCell.Row, 3)
should be
findClinic = sheet1.Cells(rngFound.Row, 3)

__________________
MPi²