选择列中的前n个值 [英] Selecting first n values in a column

问题描述

大家好，

我目前正在使用包含数百个列（公司）和行（日期）的Excel文件。但是，我只需要每列中的前504个值。问题是值在不同的日期开始，因此手动执行此操作将花费大量
时间。我一直在努力编写能解决这个问题的代码，但我似乎没有成功。总而言之，我需要一个代码来查找列中的前504个值，复制这些值，然后将它们粘贴到一个单独的文件中。在找到
之后，列必须移动到下一列的第一个值，依此类推。我希望有人可以帮助我。

提前致谢，

Wes 

解决方案

< blockquote>

所以你是说日期可能不在每一行，所以有差距？因此，您需要前504个非零值？

这些值是否需要完全复制到单独的文件中或没有间隙？

如果是这样，以下内容可以帮助您开始

 Sub Test（）
 Dim LastRowNumber As Long 
 Dim Count As Integer 
 Dim RngFirm作为范围
 Dim RngDate作为范围
设置RngFirm =范围（"B2"）'第一个公司名称
 LastRowNumber = RngFirm.SpecialCells（xlCellTypeLastCell）.Row 
 
 Do Until IsEmpty（RngFirm）
设置RngDate = RngFirm.Offset（1,0）
计数= 0 
 Do RngDate.Row> = LastRowNumber 
如果不是IsEmpty （RngDate）然后
计数=计数+ 1 
如果计数> = 504则
'复制数据
计数= 0 
退出Do 
结束If 
结束如果
设置RngDate = RngDate.Offse t（1,0）
循环
如果计数> 0然后
'复制数据（将少于504行）
结束如果
 
设置RngFirm = RngFirm.Offset（0,1）
循环
结束子
 

显然未经过正确测试！

Hello everyone,

I am currently working with an Excel file containing hundreds of columns (firms) and rows (dates). However, I only need the first 504 values in each column. The issue is that the values start at different dates, so to do this manually will take a lot of time. I have been struggling to write code that takes care of this problem, but I do not seem to succeed. So in summary I need a code that looks for the first 504 values in a column, copies these and subsequently pastes these in a seperate file. After finding the first values the column has to move to the next column and so on. I hope someone can help me.

Thanks in advance,

Wes 

解决方案

So are you saying the dates may not be in each row so there are gaps? You therefore need the first 504 nonzero values?

Do these values need to be copied exactly as is into the separate file or without the gaps?

If so the following gets you started

Sub Test()
Dim LastRowNumber As Long
Dim Count As Integer
Dim RngFirm As Range
Dim RngDate As Range
    Set RngFirm = Range("B2") 'First firm name
    LastRowNumber = RngFirm.SpecialCells(xlCellTypeLastCell).Row
    
    Do Until IsEmpty(RngFirm)
        Set RngDate = RngFirm.Offset(1, 0)
        Count = 0
        Do Until RngDate.Row >= LastRowNumber
            If Not IsEmpty(RngDate) Then
                Count = Count + 1
                If Count >= 504 Then
                    'Copy data
                    Count = 0
                    Exit Do
                End If
            End If
            Set RngDate = RngDate.Offset(1, 0)
        Loop
        If Count > 0 Then
            'Copy data (will be less than 504 rows)
        End If
        
        Set RngFirm = RngFirm.Offset(0, 1)
    Loop
End Sub

Obviously not tested properly!

这篇关于选择列中的前n个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！