도움되는 블로그

엑셀의 한 행에 위치한 값들의 배열이라고 생각할 수 있습니다.

엑셀의 한 시트 안에 있는 값들의 배열이라고 생각할 수 있습니다. 배열이 (i, p)와 같은 좌표로 구성이 됩니다.

여러 시트(sheet1, sheet2, sheet3.....)로 구성된 엑셀 파일에 있는 값들의 배열이라고 생각할 수 있습니다. 배열이 (i, p, o)와 같은 좌표로 구성됩니다.

Dim varArray(1 to 10)

Dim varArray(1 to 10, 1 to 20)    '행은 10, 열은 20인 직사각형 배열

Dim varArray()

...

...                '배열에 필요한 크기 n을 구함

Redim varArray(n)            'varArray() 배열을 크기 n의 정적배열으로 다시 선언

Dim varArray()

...

...                '배열에 필요한 행 크기 n, 열 크기 m을 구함

Redim varArray(1 to n, 1 to m)                'varArray() 배열을 행 크기 n, 열 크기 m의 정적배열으로 다시 선언

이전 포스팅 바로가기

[엑셀 VBA #9] 값이 있는 마지막 셀을 선택, End 속성

예제6.xlsm

Sub example_6()

Dim c as Range

Dim StrFirstaddr as String

Dim StrAddr as String

With Sheet7.Range("a1:a31")

    Set c = .Find(Cells(1, 5).Value, Lookat:=xlWhole)       '부분검색은 Lookat:xlPart

    If Not c Is Nothing Then                'c라는 값이 검색된다면 (c가 데이터베이스에 존재한다면)

    StrFirstaddr = c.Address            '첫 주소 설정 (무한루프 방지용)

    StrAddr = c.Address                '주소값 설정

        Do

        Range(StrAddr).Resize(, 3).Copy Cells(Rows.Count, 6).End(3)(2)        '값이 존재하는 셀 바로 아래에 복사하기

        Set c = .FindNext(c)                                '다음 c 값 찾기

        StrAddr = c.Address                                '셀 주소 설정

        Loop While Not c Is Nothing And StrAddr <> StrFirstaddr            '첫 주소와 겹치지 않을때까지 루프

    End If

End With

End Sub

이전 포스팅 바로가기

[엑셀 VBA #8] Resize, Offset 문을 이용한 셀 범위 설정

Cells(8, 7).End(1).Select

Cells(3, 3).End(2).Select

Cells(Rows.Count, 7).End(3).Select

Cells(8, Columns.Count).End(1).Select

Cells(Rows.Count, 7).End(3).Offset(1,0).Select

Option Explicit                '변수가 선언(dim) 없이 사용되는 것을 방지

Sub example()

Dim b As Object

Dim c As Object

Dim d As Object

Dim wks As Worksheet

Dim stryear As String

Dim currrow As Integer

Dim currcol As Integer

Dim curval As Long

With ActiveWorkbook                            '해당 워크북에서

    For Each wks In Worksheets                '모든 워크시트를 순환

        If InStr(wks.Name, "20") <> 0 Then            '해당 워크시트의 이름에 20이 들어가면

                 With wks.Range("a1:z20")                                    'range 범위는 모든 값을 포함하는 범위로 설정

                      Set b = .Find("자산총계", lookat:=xlWhole)            '"자산총계" 값이 있는 셀을 찾는다

                        If Not b Is Nothing Then                    '"자산총계" 값이 하나라도 있다면

                        curval = b.Offset(1, 0).Value                'curval(변수)는 "자산총계" 셀 바로 아래의 셀 값으로 설정

                      End If

                 End With

stryear = Mid(wks.Name, InStr(wks.Name, "20"), 4)                    'stryear(변수)는 시트 이름에 포함된 년도값(20xx)

                  With COMP.Range("a1:z20")                    'COMP 시트에서 (Range 범위는 모든 값을 포함하는 범위)

                  Set c = .Find(stryear, lookat:=xlPart)            'stryear값을 포함하는 셀을 찾는다.

                       If Not c Is Nothing Then                    'stryear 값을 포함하는 셀이 하나라도 있다면

                        currrow = c.Row                    'currrow(변수) 값은 그 셀의 행값

                        End If

                   Set d = .Find("자산", lookat:=xlWhole)            '"자산"을 포함하는 셀을 찾는다

                    If Not d Is Nothing Then            

                     currcol = d.Column                        'currcol(변수) 값은 그 셀의 열값

                   End If

                  End With

COMP.Cells(currrow, currcol).Value = curval       '년도와 항목에 맞춰서 값을 집어넣는다         '

        End If

    Next wks                    '다음 시트로 넘어가기

End With

 MsgBox "입력 완료"

End Sub

이전 포스팅 바로가기

[엑셀 VBA #7] SELECT CASE 조건문 활용하기

Cells(2,1).Resize(, 2).Select

Cells(3,8).offset(, -1).Select

Cells(3, 8).Offset(, -1).Resize(, 2).Select

실전예제1.txt

실전예제1.xlsm

Sub text_to_excel()

    Const loadf As Long = 2             '텍스트 파일 속에서 가져와야 하는 시작하는 행 위치

    Const loadt As Long = 99999            '텍스트 파일 속에서 가져와야 하는 마지막 행 위치보다 큰 값을 설정

    Dim strFileName As String              '텍스트 파일 이름 설정

    Dim objText As Object           '텍스트 문서 값 개체변수

    Dim i As Long                       '변하는 행값 변수

    Dim varValue As Variant           '엑셀에 입력되는 값

    Application.ScreenUpdating = False

    With Application.FileDialog(msoFileDialogFilePicker)

                             .Show              '파일피커 열기

        If .SelectedItems.Count = 0 Then        '아무것도 선택되지 않았을 경우 프로시저 종료

            Exit Sub

        Else

            strFileName = .SelectedItems(1)        '파일 이름 지정

        End If

    End With

    If Len(strFileName) > 0 Then                   '파일 이름의 길이가 0보다 크면 (파일이 존재하면)

        Set objText = CreateObject("Scripting.FileSystemObject").OpenTextFile(strFileName, _

        IOMode:=1, Create:=False, Format:=-2)           '지정된 이름의 파일을 열기

        For i = 1 To loadt                      '지정한 행 수 만큼 반복하기

            If Not objText.AtEndOfStream Then           '텍스트파일의 끝이 아니라면

            If i < loadf Then                            '행의 위치가 지정된 시작 행(loadf)보다 작다면

            objText.skipline                            '그 행을 뛰어 넘는다

            Else

                varValue = Split(objText.ReadLine, vbTab)         '텍스트파일의 한 행의 값을 탭으로 분리해서 가져온다    

'vbTab을 "," ";" " " 등으로 바꿔서 사용할 수 있다.

                Cells(Rows.Count, 1).End(3)(2).Resize(, UBound(varValue) + 1) = varValue        '셀에 텍스트파일 값을 넣기                  

            End If

            End If

        Next i

    End If

       objText.Close

    Set objText = Nothing

End Sub

Sub text_to_excel()

    Const loadf As Long = 2             '텍스트 파일 속에서 가져와야 하는 시작하는 행 위치

    Const loadt As Long = 99999            '텍스트 파일 속에서 가져와야 하는 마지막 행 위치보다 큰 값을 설정

    Dim strFileName As String              '텍스트 파일 이름 설정

    Dim objText As Object           '텍스트 문서 값 개체변수

    Dim i As Long                       '변하는 행값 변수

    Dim varValue As Variant           '엑셀에 입력되는 값

    Application.ScreenUpdating = False

    With Application.FileDialog(msoFileDialogFilePicker)

                             .Show              '파일피커 열기

        If .SelectedItems.Count = 0 Then        '아무것도 선택되지 않았을 경우 프로시저 종료

            Exit Sub

        Else

            strFileName = .SelectedItems(1)        '파일 이름 지정

        End If

    End With

    If Len(strFileName) > 0 Then                   '파일 이름의 길이가 0보다 크면 (파일이 존재하면)

        Set objText = CreateObject("Scripting.FileSystemObject").OpenTextFile(strFileName, _

        IOMode:=1, Create:=False, Format:=-2)           '지정된 이름의 파일을 열기

        For i = 1 To loadt                      '지정한 행 수 만큼 반복하기

            If Not objText.AtEndOfStream Then           '텍스트파일의 끝이 아니라면

            If i < loadf Then

            objText.skipline

            Else

                varValue = Split(objText.ReadLine, vbTab)         '텍스트파일의 한 행의 값을 탭으로 분리해서 가져온다

                Cells(Rows.Count, 1).End(3)(2).Resize(, 1) = varValue(1)        '셀에 텍스트파일 값을 넣기      '

            End If

            End If

        Next i

    End If

        objText.Close

  '(a2:a99999) 값은 평균 낼 데이터를 포함하는 범위

        Cells(2, 5).value = "=average(a2:a99999)"                'average 함수식을 셀(E5)에 집어넣는다

        Cells(2, 5).value = Cells(2, 5).value                         '출력된 함수값을 일반 형식의 값으로 바꾼다.

        Range("a2:a99999").Value = ""                                 '텍스트파일에서 추출한 데이터를 삭제한다

    Set objText = Nothing

End Sub

이전 포스팅 바로가기

[엑셀 VBA #6] IF~ELSE 조건문 활용하기

예제5.xlsm

Select case 변수

case n1

명령

case n2

명령2

...

End Select

Dim i As Byte

Dim p As Byte

For i = 3 To 17 Step 1

p = Sheet6.Cells(i,2).Value

Select Case p

Case Is >= 90

Sheet6.Cells(i, 3).Value = "A"

Case Is >= 70

Sheet6.Cells(i, 3).Value = "B"

Case Is >= 50

Sheet6.Cells(i, 3).Value = "C"

Case Is >= 30

Sheet6.Cells(i, 3).Value = "D"

Case Is < 30

Sheet6.Cells(i, 3).Value = "F"

End Select

Next i

Dim i As Byte

Dim p As Byte

For i = 3 To 17 Step 1

p = Sheet6.Cells(i, 2).Value

Select Case p

Case Is < 30

Sheet6.Cells(i, 3).Value = "F"

Case Is >= 30

Sheet6.Cells(i, 3).Value = "D"

Case Is >= 50

Sheet6.Cells(i, 3).Value = "C"

Case Is >= 70

Sheet6.Cells(i, 3).Value = "B"

Case Is >= 90

Sheet6.Cells(i, 3).Value = "A"

End Select

Next i

이전 포스팅 바로가기

[엑셀 VBA #5] FOR~NEXT 반복문 응용하기

예제4.xlsm

If 조건1 and 조건2 ... 조건n

명령

else

명령

end if

Dim i As Byte

For i = 3 To 10 Step 1           '3번째 행부터 10번째 행까지 색을 입혀야 하기 때문에 행을 i 로 하는 반복문을 만듭니다.

If Sheet5.Cells(i, 2).Value <= 60 Then                '셀의 값이 60보다 작거나 같을 경우

Sheet5.Cells(i, 1).Interior.Color = RGB(100,100,100)                '왼쪽 셀의 색깔을 RGB(100,100,100)으로 바꿉니다.

Else                   '60보다 같거나 작은 경우, 60보다 큰 경우, 두 가지뿐이 없기 때문에 Else 이하 명령은 생략합니다.

End If

Next i

Dim i As Byte

For i = 14 To 21 Step 1

    If Sheet5.Cells(i, 2).Value = 0 Then

    Sheet5.Cells(i, 1).Interior.Color = RGB(100, 10, 10)

    Else

        If Sheet5.Cells(i, 2).Value = 100 Then

        Sheet5.Cells(i, 1).Interior.Color = RGB(200, 200, 200)

        Else

            If Sheet5.Cells(i, 2).Value <= 60 Then

            Sheet5.Cells(i, 1).Interior.Color = RGB(100, 100, 100)

            Else

            End If

        End If

    End If

Next i