Excel/Excel VBA Macro

Advertisement


Question
Could use some help with Excel macro VBA calculating Unit of Measure forwards and backwards for inventory purposes. For example;

A1 = Metal1
B1 = Sq. Ft. On-Hand
C1 = Sheets On-Hand
D1 = Packs On-Hand

where C1 = B1/10
where D1 = C1/10

A2 = Metal2
B2 = Sq. Ft. On-Hand
C2 = Sheets On-Hand
D2 = Packs On-Hand

where C2 = B2/8
where D2 = C2/8

A3 = Metal3, etch thru Metal99.
Want to allow user three count and enter possibilities:
Example:
If user enters On-Hand data in B1, VBA calculates C1 & D1
If user counts sheets & then enters On-Hand data in C1, VBA calculates B1 & D1
If user counts packs & then enters On-Hand data in D1, VBA calculates B1 & C1

Answer
Hi Steve,

I've managed to put together a quick macro for what you need.

I'm assuming that you wanted to have C2 = B2/10 and D2=C2/10 in your original question instead of having the denominator as 8.

If I'm right, then you can use the below macro and see if it serves your purpose.

Hope this helps.

Private Sub CommandButton1_Click()
For i = 2 To 100

sft = Cells(i, 2).Value
sht = Cells(i, 3).Value
pck = Cells(i, 4).Value
Select Case sft

Case ""

Case Is <> ""

sht = sft / 10
pck = sht / 10
Cells(i, 3).Value = sht
Cells(i, 4).Value = pck

End Select
Next i


For i = 2 To 100

sft = Cells(i, 2).Value
sht = Cells(i, 3).Value
pck = Cells(i, 4).Value
Select Case sht

Case ""

Case Is <> ""

sft = sht * 10
pck = sht / 10
Cells(i, 2).Value = sft
Cells(i, 4).Value = pck

End Select
Next i


For i = 2 To 100

sft = Cells(i, 2).Value
sht = Cells(i, 3).Value
pck = Cells(i, 4).Value
Select Case pck

Case ""

Case Is <> ""

sft = pck * 10 * 10
sht = pck * 10
Cells(i, 2).Value = sft
Cells(i, 3).Value = sht

End Select
Next i



End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.