HELP TO STRUCTURE THE CODE

[DAGUET]

Dear All

I have tried to structure a skeleton of code in plain English describing what I want in the file.
Now I need your help to structure it the way it should. I think all the bits are here but they have to be glued together in VBA lingo.

Please try to bring this to another level
Many many thanks

 

[rahulshewale1]

Hi @DAGUET ,

Try this ?

Option Explicit

Sub Copy_Data()

Dim lrow As Long, i  As Long

'' Find Last row
lrow = Range("B1000").End(xlUp).Row

'' Declare Sheet
Dim sh1 As Worksheet
Set sh1 = Sheets("Sheet1")

Dim sh2 As Worksheet
Set sh2 = Sheets("sheet2")

For i = 2 To lrow
    Range("CompanyTicker").Value = sh2.Range("B" & i).Value
    Range("Date").Value = sh2.Range("C" & i).Value
            
            
    ' Part two
    sh2.Range("D" & i).Value = Range("TopTen").Value
    sh2.Range("E" & i).Value = Range("InstOwner").Value
    sh2.Range("F" & i).Value = Range("HedgeF").Value
            
Next i

End Sub

 

[DAGUET]

Hi @DAGUET ,

Try this ?

Option Explicit

Sub Copy_Data()

Dim lrow As Long, i  As Long

'' Find Last row
lrow = Range("B1000").End(xlUp).Row

'' Declare Sheet
Dim sh1 As Worksheet
Set sh1 = Sheets("Sheet1")

Dim sh2 As Worksheet
Set sh2 = Sheets("sheet2")

For i = 2 To lrow
    Range("CompanyTicker").Value = sh2.Range("B" & i).Value
    Range("Date").Value = sh2.Range("C" & i).Value
           
           
    ' Part two
    sh2.Range("D" & i).Value = Range("TopTen").Value
    sh2.Range("E" & i).Value = Range("InstOwner").Value
    sh2.Range("F" & i).Value = Range("HedgeF").Value
           
Next i

End Sub

Dear rahulshewale1,

sounds like it works!!!
Thank you so much
all the best

 

[rahulshewale1]

Dear rahulshewale1,

sounds like it works!!!
Thank you so much
all the best

Welcome

 

[DAGUET]

Rahul
just a quick issue : when I download the data from Bloomberg, it works but the data which is stored is automatically aligned on the last stock and does not keep the memory of the prior stock. if I want to keep the data from first to next and not copy paste the last data set and apply it to the whole : how can I do it? see file
thanks

 

[DAGUET]

In fact what I need is to insert a 'timer' ie a time lag between the execution of two consecutive loops. How would i do that?

 

[rahulshewale1]

Hi @dauget,

Sorry I am not clear questions ,if you post sample sheet what results you are expecting then I shall help you .

 

[DAGUET]

Ok Rahul I am sorry
I rephrase it. How could I insert a 'timer' between two loops (10''-15'') ? see file att