some vba coding from pseudo code

vofzxy

New Member
Joined
May 2, 2013
Messages
8
good day all, i have a "simple" issue, it's just i do not know how vba works,
i have done basic C /matlab but thats about it

ok here goes i have 2 problems i HOPE to solve

situation: 2 cols of data (54k long ><" ) col A & B
aim: copy data from col B to col A, only if the cell in col A is blank

PSEUDO CODE:

//I KNOW LOL, THANKS FOR STARTING AT 1 NOT 0
VAR X =1


// check cell AX is empty, else copy from cell BX
if sheet.cell.A(X) == isblank();
sheet.cell.A(X) = sheet.cell.B(X)


//check for last row
if sheet.cell.A(X) == isblank() & sheet.cell.B(X) == isblank();
exit

// increase X
X++

//loop?


2nd situation: i have data is MM/DD/YYYY, I want to convert them to YYYYMMDD

i really do not have any idea on this, i am thinking in terms of reading each digit putting them in some LONG CHAR
or individual "BOXES" and reading them backwards

is it possible?

cheers

thanks so much.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test2()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"
    .Value = .Value
End With
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
For the first part, perhaps this.
Code:
Dim rng As Range

     Set rng = Range("A1")

     While rng.Value <> "" And rng.Offset(,1).Value<>""

           If rng.Value = "" Then
                 rng.Value = rng.Offset(,1).Value
           End If

           Set rng = rng.Offset(1)
     Wend
That's a kind of brute force method, there might be a more elegant way to do.

For the second part, if you have real dates you can just format them to display as you want.
 

vofzxy

New Member
Joined
May 2, 2013
Messages
8
For the first part, perhaps this.

That's a kind of brute force method, there might be a more elegant way to do.

For the second part, if you have real dates you can just format them to display as you want.

heys man, thanks for sharing.
nah the formatting does not work, cause changing from "date" to "general" will produce random numbers or sorts.


EDIT: i must admit something, i do not know what to do with ur code :(
 
Last edited:

jimmy90

Board Regular
Joined
May 1, 2013
Messages
80

ADVERTISEMENT

Code:
For Each ACell In Range("B1", Range("B" & Rows.Count).End(xlUp).Row   
    If IsEmpty(ACell.Offset(,-1)) Then
       ACell.Offset(,-1).Formula = ACell.Formula
    End If
Next

As far as the Dates Click on the letter at the top of the Column then right click a cell. then Select "Format Cells..." from the context menu. Under the Number Tab Select Custom and in the text Box enter, "yyyymmdd"
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
If you have real dates then formatting should work.

Though you would change the formatting to Custom YYYYMMDD not General.

If you format as General you will see the date values as they are actually stored in Excel, not random numbers.
 

vofzxy

New Member
Joined
May 2, 2013
Messages
8

ADVERTISEMENT

Rich (BB code):
For Each ACell In Range("B1", Range("B" & Rows.Count).End(xlUp).Row)   //syntax error, i added the )
    If IsEmpty(ACell.Offset(,-1)) Then
       ACell.Offset(,-1).Formula = ACell.Formula
    End If
Next

i will try the DATES thing again. cheers

back to the ), i get "method 'range' of object '_global' failed "


**** i need a vba book
 

vofzxy

New Member
Joined
May 2, 2013
Messages
8
Try

Code:
Sub test2()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"
    .Value = .Value
End With
End Sub

sorry, i did not refresh the page in time,
OMG it works

urm, i can only say thanks , but i wish i could do more.

PS, do you have any pointers how i can get around starting vba/ pivot tables, books perhaps?
honestly, yes it is out of necessity of the job and i am "lazy" that i am asking.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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
Top