how to write vlookup in vba code

M

monu

New Member
Joined
Jun 21, 2011
Messages
11
I have a sheet with several column- stocks name,future price, spot price etc.
How to use vlookup function the data stocks and its future price in seperate sheet.
Exp:
Stocks spotprice future price

icici 232 256
idbi 58 62
....
....
...
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi

Can you elaborate a little, I am not sure if your Source data is in 2 locations or you wish the results to put into 2 different location.


regards

Saltkev
 
Upvote 0
<TABLE style="WIDTH: 272pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=363 border=0><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 80pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=107 height=17>stock </TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>p1 p2</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64> p3</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64> p4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>20MICRONS</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>42.5</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>43.15</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>45.45</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>48.2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3IINFOTECH</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>44.2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>43.95</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>45.15</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>46.8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3MINDIA</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>3917.5</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>3914.8</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>3901.65</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>3895.55</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A2ZMES</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>215.95</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>219.4</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>223.75</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>229.2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AARTIDRUGS</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>132.6</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>128.65</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>311.1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>318.95</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AARTIIND</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>51.55</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>51.5</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>131.35</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>131.25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AARVEEDEN</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>52.2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>49.5</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>51.5</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>51.05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABAN</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>541.8</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>538.45</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>50.9</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>50.6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABB</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>860.8</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>856</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>560.7</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>566.85</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABBOTINDIA</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1432</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1419.95</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>850.1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>858.65</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABCIL</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>112.9</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>111.6</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1419.4</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1428.1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABGSHIP</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>356.45</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>360.25</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>113.35</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>114.65</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABHISHEK</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>15.7</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>15.8</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>372.85</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>373.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABIRLANUVO</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>820.1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>846.3</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>16</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>15.8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACC</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>999.9</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1001.6</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>849.65</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>884.05</TD></TR></TBODY></TABLE>
___________
Now i want the p1 value of stocks in different sheet.
 
Upvote 0
Hi

give this a try, you can change the ranges to suit your own requirements. Can be run as spread sheet event or under a command button


Code: 
Dim Vlooker
Application.ScreenUpdating = False
 
For Each Vlooker In Sheets("sheet1").Range("a4:a7")  'Where to look first col
 
 
        If Vlooker.Value = Sheets("sheet1").Range("a1").Value Then  ' Search Criteria
                Vlooker.Offset(0, 1).Select ' make selection from 2nd Col
                Selection.Copy
 
                 Sheets("Sheet2").Activate ' copy to Sheet2
 
 
              If Sheets("Sheet2").Range("a4").Value = "" Then
               Sheets("Sheet2").Range("a4").Select
               ActiveSheet.Paste
               GoTo ender
 
            End If
 
 
 
         ' Paste second row in  cell "A5"
 
          Sheets("Sheet2").Activate
 
            If Sheets("Sheet2").Range("a4").Value > "" And Sheets("Sheet2").Range("a5").Value = "" Then
               Sheets("Sheet2").Range("a5").Select
               ActiveSheet.Paste
               GoTo ender
 
            End If
 
         ' Paste subsequent rows in  first available cell at bottom
          Sheets("Sheet2").Activate
 
            If Sheets("Sheet2").Range("a4").Value > "" And Sheets("Sheet2").Range("a5").Value > "" Then
               Sheets("Sheet2").Range("a4").Select
               Selection.End(xlDown).Select
               Selection.Offset(1, 0).Select
               ActiveSheet.Paste
               Sheets("sheet1").Activate
            End If
 
ender:
              Sheets("sheet1").Activate
 
        End If
 
Next Vlooker


regards


Saltkev
 
Last edited:
Upvote 0
thanks for reply.
But still error is coming
For Each Vlooker In Sheets("sheet1").Range("a4:a7")
 
Upvote 0
Hi

Not sure if you are doing this, but you need to adjust the code to suite your spread sheet, I have tried to comment the pertinent lines of code to help, the range ("A4:A7") is this the range where the first col of your source data is stored, The Range "A1" needs to contain a value to search for.

Any help
 
Upvote 0
sheet 1 :

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 21pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl66 height=28 width=64>symbol</TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl66 width=64>spot</TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl66 width=64>spot_change</TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl66 width=64>spot_% chg</TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl66 width=64>future</TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl66 width=64>fut_change</TD><TD style="BORDER-BOTTOM: #333333 0.5pt solid; BORDER-LEFT: #333333; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #333333 0.5pt solid; BORDER-RIGHT: #333333 0.5pt solid" class=xl66 width=64>fut_% chg</TD></TR><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 21pt; BORDER-TOP: #993366 0.5pt solid; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=28 width=64>3IINFOTECH</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366 0.5pt solid; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>45.15</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366 0.5pt solid; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>1.20</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366 0.5pt solid; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>2.73</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366 0.5pt solid; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>45.55</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366 0.5pt solid; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>1.20</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366 0.5pt solid; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>2.71</TD></TR><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 21pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=28 width=64>ADANIPOWER</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>114.15</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>2.40</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>2.15</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>114.70</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>2.35</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>2.09</TD></TR><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 21pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=28 width=64>AMBUJACEM</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>135.90</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>-2.65</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>-1.91</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>134.80</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>-0.75</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>-0.55</TD></TR><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 21pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=28 width=64>ANDHRABANK</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>140.75</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>3.55</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>2.59</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>136.35</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>3.40</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>2.56</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=17 width=64>APIL</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>546.05</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>4.90</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>0.91</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>541.30</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>4.90</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>0.91</TD></TR><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 21pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=28 width=64>APOLLOTYRE</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>70.70</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>3.60</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>5.37</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>69.15</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>1.80</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>2.67</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=17 width=64>SUNTV</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>391.80</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>3.45</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>0.89</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>393.30</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>3.65</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>0.94</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=17 width=64>SUZLON</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>51.10</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>0.30</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>0.59</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>51.45</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>0.40</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>0.78</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=17 width=64>ABAN</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>560.70</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>22.25</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>4.13</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>565.05</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>23.30</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>4.30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=17 width=64>ABB</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>850.10</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>-5.90</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>-0.69</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>846.90</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>5.00</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>0.59</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl63 height=17 width=64>ABGSHIP</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>372.85</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>12.60</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>3.50</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>374.95</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl65 width=64>14.15</TD><TD style="BORDER-BOTTOM: #993366 0.5pt solid; BORDER-LEFT: #993366; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #993366; BORDER-RIGHT: #993366 0.5pt solid" class=xl64 width=64>3.92</TD></TR></TBODY></TABLE>


Sheet2
<TABLE style="WIDTH: 204pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=272><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=80>stocks</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>future</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>spot</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>basis</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>3IINFOTECH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>ADANIPOWER</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>AMBUJACEM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>ANDHRABANK</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>APIL</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>APOLLOTYRE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>SUNTV</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=80>SUNTV</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR></TBODY></TABLE>

i have to take future data from sheet 1 for the all stocks.

by using VBA code or vba code for vlookup.

thanks in advance
 
Upvote 0
Hi

Life would have beeen easier if we were if we had this info to start, we are not mind readers, you will get a more varied and accurate response if we all understand. I have assumed that your tables start at cell A1 in both instances. You will have to alter the ranges to suite your data if different. This does work I have tested it with your data, so you should be ok now.

1. It looks at the stocks on Sheet2
2. Searches for a match in Symbols Sheet1
3. If it finds a match it copies col "E" data sheet1 into corresponding row in col "B" Sheet2


Code: 
Dim FindString As String
Dim Rng As Range
Dim fcomp
For Each fcomp In Sheets("sheet2").Range("a2:a9") ' range of Source Comparison

    FindString = fcomp
   
        
        With Sheets("Sheet1").Range("a2:a12") 'range of cells to search
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
            
            If Rng Is Nothing Then
                
                
                
            Else
            
              fcomp.Offset(0, 1).Value = Rng.Offset(0, 4)
                
            End If
        
        End With
             
  Next fcomp

regards


Kev
 
Upvote 0
Dear kev,
you have done great job for me.
thanks again.
will you guide me, which book should i follow to learn vba
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
953
Alex Blakenburg
A
Data123
  • Solved
Symbol list in Spreadsheet
Replies
7
Views
773
Peter_SSs
Peter_SSs
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
178
Peter_SSs
Peter_SSs
P
  • Solved
VLOOKUP (or VBA Alternative?) From another workbook, depending on Financial Year
Replies
2
Views
494
paun_shotts
P
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
419
STARL0RD
S

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

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