Spreadsheet to discover city from zip code list

Question

Spreadsheet to discover city from zip code list

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

Good morning!

I'm trying to create a VBA adapted to a spreadsheet I downloaded on the internet that separates all the values from a zip code. I would like to get the city and paste next to the zip code in the spreadsheet.

I tried for myself to create a Macro that would do this and even works, but I do not know how to adapt Macro to work across the column.

Below are some photos explaining the situation:

Thanks for any help!

excel vba

asked by anonymous 14.09.2017 / 15:20

2 answers

Multi-Downloads with PHP and mPDF Using a root directory in the NodeJS include

score 2 · Answer 1

Range.Copy will be used as it is simple and easy. Select should be avoided in excel-vba . If you want to use .Select it is recommended to turn off the screen update before the code starts and reconnect at the end. Application.ScreenUpdating = False and Application.ScreenUpdating = True

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("CONSULTAR")
Set ws2 = ThisWorkbook.Sheets("CIDADES")
'Pode usar .Sheets ou .Worksheets, com o nome entre "" ou com o número de index
'Exemplo de index
'Set ws1 = ThisWorkbook.Sheets(3)
'Set ws2 = ThisWorkbook.Worksheets(4)
rLast = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row + 1

ws1.Range("D6").Copy Destination:=ws2.Cells(rLast, 1)
ws1.Range("E14").Copy Destination:=ws2.Cells(rLast, 2)

The code can be executed by a button or by events, for example: change in the CONSULT worksheet.

1. Worksheet Statement

First you declare each worksheet used, so you can copy from one to another

Dim ws1 As Worksheet :Set ws1 = ThisWorkbook.Sheets("CONSULTAR")

2. Get the last line

The code ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row gets the last row of column 1, ie "A". And then% w of% sum 1 to write 1 line after the last.

rLast = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row + 1

3. Copy and Paste

rLast copies cell D6 from Worksheet CONSULT

ws1.Range("D6").Copy paste in column 1 ("A") and rLast

ws1.Range("D6").Copy Destination:=ws2.Cells(rLast, 1)

Note: Next time you enter the code in formatting correct and not by image.

score 1 · Answer 2

I stayed in the searches here and I just managed to do the following:

Sub Calcular()
'
' Calcular Macro
'
' Atalho do teclado: Ctrl+Shift+C
'

    Dim i As Long
    For i = 2 To Rows.Count

    Sheets("CIDADES").Select
    Range("A" & i).Select

    If ActiveCell.Value = "" Then
        MsgBox "Fim!"
        Exit Sub
    Else

    Selection.Copy
    Sheets("CONSULTAR").Select
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("CONSULTAR").Select
    Range("E14:J14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CIDADES").Select
    Range("B" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    End If

    Next i

End Sub